#include "StdAfx.h"
#include "MySQLEx.h"
#include "Celsus.h"



CMySQLEx::CMySQLEx(void)
{
	mysql_init( &m_mysql );
}

CMySQLEx::~CMySQLEx(void)
{
	mysql_close( &m_mysql );
	mysql_library_end();
}

void CMySQLEx::SetConnection(LPCTSTR lpszHostName, LPCTSTR lpszDatabase, LPCTSTR lpszUserName, LPCTSTR lpszPassword)
{
	m_szHostName = lpszHostName;
	m_szDatabase = lpszDatabase;
	m_szUserName = lpszUserName;
	m_szPassword = lpszPassword;
}

void CMySQLEx::SetExternalConnection(LPCTSTR lpszHostName, LPCTSTR lpszDatabase, LPCTSTR lpszUserName, LPCTSTR lpszPassword)
{
	m_szHostNameExternal = lpszHostName;
	m_szDatabaseExternal = lpszDatabase;
	m_szUserNameExternal = lpszUserName;
	m_szPasswordExternal = lpszPassword;
}

BOOL CMySQLEx::Connect(BOOL bExternal)
{
	USES_CONVERSION;

	CString szHostName;
	CString szDatabase;
	CString szUserName;
	CString szPassword;

	// decide which databse
	if( !bExternal )
	{
		szHostName = m_szHostName;
		szDatabase = m_szDatabase;
		szUserName = m_szUserName;
		szPassword = m_szPassword;
	}
	else if( bExternal && !m_szHostNameExternal.IsEmpty() )
	{
		szHostName = m_szHostNameExternal;
		szDatabase = m_szDatabaseExternal;
		szUserName = m_szUserNameExternal;
		szPassword = m_szPasswordExternal;
	}
	else
	{
		return FALSE;
	}

	// may as well close and init the connection just in case
	mysql_close( &m_mysql );
	mysql_init( &m_mysql );

	// connect to database
	if( !mysql_real_connect( &m_mysql, T2A(szHostName), T2A(szUserName), T2A(szPassword), T2A(szDatabase), 0, NULL, 0 ))	
	{
		CString szError(mysql_error( &m_mysql ));
		CString szMessage;

		szMessage.Format( _T("ERROR: Connecting to MySQL Server.\n\r\n\rReason: %s"), szError );
		AfxMessageBox( szMessage );
		Close();
		PostQuitMessage( 1001 );
		return FALSE;
	}
	return TRUE;
}

void CMySQLEx::Close()
{
	mysql_close( &m_mysql );
}

BOOL CMySQLEx::UpdateDatabase(void)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		MessageBox( NULL, _T("Error getting database version."), _T("ERROR:"), MB_OK );
		return FALSE;
	}

	CString szQuery;
	INT nVersion = 0;
	MYSQL_RES *res;
	MYSQL_ROW row;

	// get version
	szQuery = _T("SELECT version FROM database_version");
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) == S_OK )
	{
		// get results
		res = mysql_use_result( &m_mysql );
		if( res )
		{
			row = mysql_fetch_row( res );
			nVersion = GetSQLInt(row[0]);
			mysql_free_result( res );
		}
		else
		{
			CString szError(mysql_error( &m_mysql ));
			AfxMessageBox( szError );
			Close();
			return FALSE;
		}
	}

	// Check database version
	if( nVersion < DATABASE_VERSION )
	{
		ASSERT( FALSE );
		MessageBox( NULL, _T("This version of Celsus requires a newer database version.\n\nPlease update your database to the current version."), _T("WARNING:"), MB_OK );
		return FALSE;
	}

	// return
	return TRUE;
}

CString CMySQLEx::GetSQLString(char* szText)
{
	CString szValue(szText);
	return szValue;
}

ULONG CMySQLEx::GetSQLULong(char* szText)
{
	CString szValue(szText);
	ULONG nValue;
	nValue = _tstol(szValue);
	return nValue;
}

LONG CMySQLEx::GetSQLLong(char* szText)
{
	CString szValue(szText);
	LONG nValue;
	nValue = _tstol(szValue);
	return nValue;
}

DOUBLE CMySQLEx::GetSQLDouble(char* szText)
{
	CString szValue(szText);
	DOUBLE nValue;
	if( szValue.IsEmpty() )
		return 0.0;
	nValue = _tstof(szValue);
	return nValue;
}

INT CMySQLEx::GetSQLInt(char* szText)
{
	CString szValue(szText);
	INT nValue;
	nValue = _tstoi(szValue);
	return nValue;
}

CString CMySQLEx::ES(CString String)
{
	USES_CONVERSION;

	LONG nLength = String.GetLength();

	if( nLength <= 0 )
		return _T("");

	char* NewString = new char[nLength*2+1];
	mysql_real_escape_string( &m_mysql, NewString, T2A(String), nLength );

	CString szReturn( NewString );
	delete NewString;

	return szReturn;
}

CString CMySQLEx::GetTypeTable(INT nType)
{
	CString szTable;
	switch( nType )
	{
		case collection:
			szTable = _T("description_collection");
			break;
		case method:
			szTable = _T("description_method");
			break;
		case priority:
			szTable = _T("description_priority");
			break;
		case status:
			szTable = _T("description_status");
			break;
		case library:
			szTable = _T("description_library");
			break;
		case ill:
			szTable = _T("description_ill");
			break;
		case referrals:
			szTable = _T("description_referrals");
			break;
		case copyright:
			szTable = _T("description_copyright");
			break;
		case user_type:
			szTable = _T("description_user_type");
			break;
		case staff:
			szTable = _T("staff");
			break;
		case users:
			szTable = _T("users");
			break;
		case requests:
			szTable = _T("requests");
			break;
		case external_ill:
			szTable = _T("external_ill");
			break;
		case external_ill_history:
			szTable = _T("external_ill_history");
			break;
		case request_links:
			szTable = _T("request_links");
			break;
		case source_links:
			szTable = _T("source_links");
			break;
	}
	return szTable;
}

void CMySQLEx::GetFieldNames(MYSQL_RES *res)
{
	USES_CONVERSION;

	MYSQL_FIELD *fields;
	
	UINT nFieldsCount = mysql_num_fields( res );
	fields = mysql_fetch_fields( res );

	// fill arrray
	for(UINT i = 0; i < nFieldsCount; i++)
		m_sFields.Add( A2T(fields[i].name) );
}

LONG CMySQLEx::IndexByName(MYSQL_RES *res, LPCTSTR pName, LPCTSTR pTable)
{
	USES_CONVERSION;

	MYSQL_FIELD *fields = NULL;
	UINT nFieldsCount = 0;

	nFieldsCount = mysql_num_fields( res );
	fields = mysql_fetch_fields( res );
	for(UINT nIndex = 0; nIndex < nFieldsCount; nIndex++)
	{
		if( _tcsicmp( pName, A2T(fields[nIndex].name )) == 0 )
		{
			if( pTable == NULL )
			{
				return nIndex;
			}
			else
			{
				if( _tcsicmp( pTable, A2T(fields[nIndex].table )) == 0 )
					return nIndex;
			}
		}
	}
	ASSERT(FALSE);
	return -1;
}

MYSQL_RES* CMySQLEx::QueryForRow(LPCTSTR lpszQuery, MYSQL_ROW *row)
{
	USES_CONVERSION;

	MYSQL_RES *res = NULL;

	// query
	if( mysql_real_query( &m_mysql, T2A(lpszQuery), _tcslen( lpszQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return NULL;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return NULL;
	}

	// only one returned
	*row = mysql_fetch_row( res );

	// check result
	if( row == NULL )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return NULL;
	}
	return res;
}

CString CMySQLEx::GetMessageSubject(INT nType, LONG nID)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return NULL;
	}

	CString szMessageSubject;
	CString szQuery;
	CString szTable;
	MYSQL_RES *res = NULL;
	MYSQL_ROW row;

	// get table
	szTable = GetTypeTable( nType );

	// set table
	szQuery.Format( _T("SELECT message_subject FROM %s WHERE id=%ld"), ES(szTable), nID );

	res = QueryForRow( szQuery, &row );

	// get description
	if( res && res->row_count == 1 )
	{
		szMessageSubject = GetSQLString(row[0]);
	}
	mysql_free_result( res );

	Close();
	return szMessageSubject;
}

CString CMySQLEx::GetDescription(INT nType, LONG nID)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return NULL;
	}

	CString szDescription;
	CString szQuery;
	CString szTable;
	MYSQL_RES *res = NULL;
	MYSQL_ROW row;

	// get table
	szTable = GetTypeTable( nType );

	// set table
	szQuery.Format( _T("SELECT description FROM %s WHERE id=%ld"), ES(szTable), nID );

	res = QueryForRow( szQuery, &row );

	// get description
	if( res && res->row_count == 1 )
	{
		szDescription = GetSQLString(row[0]);
	}
	mysql_free_result( res );

	Close();
	return szDescription;
}

ULONG CMySQLEx::GetDescriptions(INT nType, CStringArray* psaDescriptions, INT nFormat, CArray<LONG>* pnaID)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	CString szQuery;
	CString szTable;
	MYSQL_RES *res = NULL;
	MYSQL_ROW row;

	// empty arrays
	psaDescriptions->RemoveAll();
	if( pnaID )
		pnaID->RemoveAll();

	// get table
	szTable = GetTypeTable( nType );

	// set table
	if( nFormat == list_ILL )
		szQuery.Format( _T("SELECT id, description, type FROM %s ORDER BY id"), ES(szTable) );
	else
		szQuery.Format( _T("SELECT id, description FROM %s ORDER BY id"), ES(szTable) );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	// get list
	if( nFormat == list )
	{
		CString szCollection;
		// populate array
		while( row = mysql_fetch_row( res ))
		{
			INT nRow = 0;
			// description array
			LONG nID = GetSQLLong(row[nRow++]);
			CString szDescription = GetSQLString(row[nRow++]);

			szCollection.Format( _T("%.3d   %s"), nID, szDescription );
			psaDescriptions->Add( szCollection );
			// id array
			if( pnaID )
				pnaID->Add( nID );
		}
	}
	else if( nFormat == list_ILL )
	{
		CString szCollection;
		// populate array
		while( row = mysql_fetch_row( res ))
		{
			INT nRow = 0;
			// description array
			LONG nID = GetSQLLong(row[nRow++]);
			CString szDescription = GetSQLString(row[nRow++]);
			INT nType = GetSQLInt(row[nRow++]);

			// check for ILL
			if( nType & ID_INTERNAL_ILL_LIBRARY || nType & ID_EXTERNAL_ILL_LIBRARY )
			{
				// description array
				szCollection.Format( _T("%.3d   %s"), nID, szDescription );
				psaDescriptions->Add( szCollection );
				// id array
				if( pnaID )
					pnaID->Add( nID );
			}
		}
	}
	else
	{
		// populate array
		while( row = mysql_fetch_row( res ))
		{
			INT nRow = 0;
			// description array
			LONG nID = GetSQLLong(row[nRow++]);
			CString szDescription = GetSQLString(row[nRow++]);
			psaDescriptions->SetAtGrow( nID, szDescription );
			// id array
			if( pnaID )
				pnaID->Add( nID );
		}
	}

	mysql_free_result( res );
	Close();

	return TRUE;
}

ULONG CMySQLEx::GetRequestCount(CString szFilter)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	ULONG nCount = 0;
	CString szQuery;
	MYSQL_RES *res;
	MYSQL_ROW row;

	// count requests
	szQuery = _T("SELECT COUNT(requests.id) FROM requests");

	TRACE( _T("Getting Count\n") );

	// set filter
	if( !szFilter.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE %s"), szQuery, szFilter );
		szQuery = szTemp;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	// get the row
	row = mysql_fetch_row( res );
	nCount = _tstol(A2T(row[0]));

	mysql_free_result( res );
	Close();
	
	//TRACE( _T("Count = %ld\n"), nCount );
	return nCount;
}

ULONG CMySQLEx::GetRowCount(INT nType, LONGLONG nID)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	ULONG nCount = 0;
	CString szQuery;
	CString szTable;
	MYSQL_RES *res;
	MYSQL_ROW row;

	// get table
	szTable = GetTypeTable( nType );

	// get count
	szQuery.Format( _T("SELECT COUNT(id) FROM %s WHERE id=%I64d"), ES(szTable), nID );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	// get the row
	row = mysql_fetch_row( res );
	nCount = _tstol(A2T(row[0]));

	mysql_free_result( res );
	Close();
	return nCount;
}

ULONG CMySQLEx::GetRowCount(INT nType, CString szField, CString szValue)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	ULONG nCount = 0;
	CString szQuery;
	CString szTable;
	MYSQL_RES *res;
	MYSQL_ROW row;

	// get table
	szTable = GetTypeTable( nType );

	// set table
	szQuery.Format( _T("SELECT COUNT(%s) FROM %s WHERE %s='%s'"), ES(szField), ES(szTable), ES(szField), ES(szValue) );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	// get the row
	row = mysql_fetch_row( res );
	nCount = _tstol(A2T(row[0]));

	mysql_free_result( res );
	Close();
	return nCount;
}

BOOL CMySQLEx::RemoveRow(INT nType, LONGLONG nID)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	CString szTable;

	// get table
	szTable = GetTypeTable( nType );

	// set table
	szQuery.Format( _T("DELETE FROM %s WHERE id=%I64d"), ES(szTable), nID );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}
	Close();
	return TRUE;
}

BOOL CMySQLEx::RemoveRows(INT nType, CString szField, LONGLONG nID, CString szFilter)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	CString szTable;

	// get table
	szTable = GetTypeTable( nType );

	// set table
	szQuery.Format( _T("DELETE FROM %s WHERE %s=%I64d"), ES(szTable), ES(szField), nID );

	if( !szFilter.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s AND %s"), szQuery, szFilter );
		szQuery = szTemp;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}
	Close();
	return TRUE;
}

BOOL CMySQLEx::SetCurrentDateTime(INT nType, CString szField, LONGLONG nID)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szDate;
	CString szQuery;
	CString szTable;

	// get current date time
	COleDateTime date = COleDateTime::GetCurrentTime();
	szDate = date.Format( _T("%Y-%m-%d %H:%M:%S") );

	// get table
	szTable = GetTypeTable( nType );

	// set table
	szQuery.Format( _T("UPDATE %s SET %s='%s' WHERE id=%I64d"), ES(szTable), ES(szField), szDate, nID );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}
	Close();
	return TRUE;
}

BOOL CMySQLEx::SetFieldValue(INT nType, CString szField, LONGLONG nID, LONG nValue)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	CString szTable;

	// get table
	szTable = GetTypeTable( nType );

	// set table
	szQuery.Format( _T("UPDATE %s SET %s=%ld WHERE id=%I64d"), ES(szTable), ES(szField), nValue, nID );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}
	Close();
	return TRUE;
}

BOOL CMySQLEx::SetFieldValue(INT nType, CString szField, LONGLONG nID, CString szValue)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	CString szTable;

	// get table
	szTable = GetTypeTable( nType );

	// set table
	szQuery.Format( _T("UPDATE %s SET %s='%s' WHERE id=%I64d"), ES(szTable), ES(szField), ES(szValue), nID );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}
	Close();
	return TRUE;
}

BOOL CMySQLEx::UpdateFieldValue(INT nType, CString szField, LONG nValue, LONG nNew)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	CString szTable;

	// get table
	szTable = GetTypeTable( nType );

	// set table
	szQuery.Format( _T("UPDATE %s SET %s=%ld WHERE %s=%ld"), ES(szTable), ES(szField), nNew, ES(szField), nValue );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}
	Close();
	return TRUE;
}

BOOL CMySQLEx::UpdateFieldValue(INT nType, CString szField, CString szValue, CString szNew)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	CString szTable;

	// get table
	szTable = GetTypeTable( nType );

	// set table
	szQuery.Format( _T("UPDATE %s SET %s='%s' WHERE %s='%s'"), ES(szTable), ES(szField), ES(szNew), ES(szField), ES(szValue) );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}
	Close();
	return TRUE;
}

BOOL CMySQLEx::GetInformation(INT nType, CString* szHTML, BOOL* bUserListed)
{
	USES_CONVERSION;

	ASSERT( nType >= 0 );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT html, user_listed FROM information WHERE type=%ld"), nType );

	res = QueryForRow( szQuery, &row );
	if( res && res->row_count > 0 )
	{
		INT nIndex = 0;
		// order
		*szHTML = A2T(row[nIndex++]);
		*bUserListed = _tstol(A2T(row[nIndex++]));
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::SetInformation(INT nType, CString szHTML, BOOL bUserListed)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	szQuery.Format( _T("UPDATE information SET html='%s', user_listed=%d WHERE type=%d"), ES(szHTML), bUserListed, nType );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	Close();
	return TRUE;
}

BOOL CMySQLEx::UpdateUserHistory(LONGLONG nID, CString szDescription)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szDate;
	CString szQuery;
	CString szTable;

	// get current date time
	COleDateTime date = COleDateTime::GetCurrentTime();
	szDate = date.Format( _T("%Y-%m-%d %H:%M:%S") );

	// set query
	szQuery.Format( _T("INSERT INTO user_history (user_id, date_in, description) VALUES (%I64d, '%s', '%s')"), nID, szDate, ES(szDescription) );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	Close();
	return TRUE;
}

BOOL CMySQLEx::UpdateRequestHistory(LONGLONG nRID, LONGLONG nUID, LONG nStatus, CString szDescription)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szDate;
	CString szQuery;
	CString szTable;

	// get current date time
	COleDateTime date = COleDateTime::GetCurrentTime();
	szDate = date.Format( _T("%Y-%m-%d %H:%M:%S") );

	// set query
	szQuery.Format( _T("INSERT INTO request_history (request_id, user_id, date_in, status_id, description) VALUES (%I64d , %I64d, '%s', %ld, '%s')"), nRID, nUID, szDate, nStatus, ES(szDescription) );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		CString szMessage;
		szMessage.Format( _T("UpdateRequestHistory: %s"), szError );
		AfxMessageBox( szMessage );
		Close();
		return FALSE;
	}

	Close();
	return TRUE;
}

LONG CMySQLEx::UpdateClaimLimit(INT nHours)
{
	USES_CONVERSION;
	LONG nRetVal = -1;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return nRetVal;
	}

	CString szDate;
	CString szQuery;
	CString szTable;

	// set query
	szQuery.Format( _T("UPDATE requests \
					   JOIN description_library ON description_library.id = requests.library_id \
					   SET requests.status_id=0, requests.staff_id=0, requests.touched=0 \
					   WHERE status_id=1 AND retain=0 AND date_hold != '0000-00-00 00:00:00' AND date_hold < (NOW()-INTERVAL %d HOUR) AND description_library.type ^ 0x01"), nHours );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		CString szMessage;
		szMessage.Format( _T("UpdateClaimLimit: %s"), szError );
		AfxMessageBox( szMessage );
		Close();
		return nRetVal;
	}

	// get rows affected
	nRetVal = (LONG)mysql_affected_rows( &m_mysql );

	Close();
	return nRetVal;
}

BOOL CMySQLEx::GetUserHistory(USER_HISTORY *pHistory, LONGLONG nID)
{
	USES_CONVERSION;

	ASSERT( nID >= 0 );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, user_id, date_in, description FROM request_history WHERE request_id=%I64d"), nID );

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		pHistory->m_id = GetSQLLong(row[nRow++]);
		pHistory->m_user_id = GetSQLLong(row[nRow++]);
		pHistory->m_date_in.ParseDateTime( GetSQLString(row[nRow++]) );
		pHistory->m_description = GetSQLString(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetRequestHistory(REQUEST_HISTORY *pHistory, LONGLONG nID)
{
	USES_CONVERSION;

	ASSERT( nID >= 0 );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, request_id, user_id, date_in, status_id, description FROM request_history WHERE request_id=%I64d"), nID );

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		pHistory->m_id = GetSQLLong(row[nRow++]);
		pHistory->m_request_id = GetSQLLong(row[nRow++]);
		pHistory->m_user_id = GetSQLLong(row[nRow++]);
		pHistory->m_date_in.ParseDateTime( GetSQLString(row[nRow++]) );
		pHistory->m_status_id = GetSQLLong(row[nRow++]);
		pHistory->m_description = GetSQLString(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetRequestHistorys(CArray<REQUEST_HISTORY> *pHistory, LONGLONG nID, CString szSort)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, request_id, user_id, date_in, status_id, description FROM request_history WHERE request_id=%I64d"), nID );

	// add sort
	if( !szSort.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s ORDER BY %s"), szQuery, szSort );
		szQuery = szTemp;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	REQUEST_HISTORY History;
	// clear array
	pHistory->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		History.m_id = GetSQLLong(row[nRow++]);
		History.m_request_id = GetSQLLong(row[nRow++]);
		History.m_user_id = GetSQLLong(row[nRow++]);
		History.m_date_in.ParseDateTime( GetSQLString(row[nRow++]) );
		History.m_status_id = GetSQLLong(row[nRow++]);
		History.m_description = GetSQLString(row[nRow++]);
		// insert
		pHistory->Add( History );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetRequestHistorys(CArray<REQUEST_HISTORY> *pHistory, CString szFilter, CString szSort)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, request_id, user_id, date_in, status_id, description FROM request_history") );

	// add filter
	if( !szFilter.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE %s"), szQuery, szFilter );
		szQuery = szTemp;
	}

	// add sort
	if( !szSort.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s ORDER BY %s"), szQuery, szSort );
		szQuery = szTemp;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	REQUEST_HISTORY History;
	// clear array
	pHistory->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		History.m_id = GetSQLLong(row[nRow++]);
		History.m_request_id = GetSQLLong(row[nRow++]);
		History.m_user_id = GetSQLLong(row[nRow++]);
		History.m_date_in.ParseDateTime( GetSQLString(row[nRow++]) );
		History.m_status_id = GetSQLLong(row[nRow++]);
		History.m_description = GetSQLString(row[nRow++]);
		// insert
		pHistory->Add( History );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetRequestLinkSources(CStringArray *pSources)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT source FROM request_links") );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// clear array
	pSources->RemoveAll();

	CString szSource;
	CString szSourceTitle;

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		szSource = GetSQLString(row[0]);
		szSourceTitle = szSource.Left( szSource.Find( appConfig.m_SFX.szSourceEndString ));
		// insert
		pSources->Add( szSourceTitle );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetRequestLinks(CArray<REQUEST_LINK> *pLinks, CString szUID)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, staff_id, date_in, uid, title, source, link, notes, material FROM request_links WHERE uid='%s'"), szUID );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	REQUEST_LINK Links;
	// clear array
	pLinks->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		Links.m_id = GetSQLLong(row[nRow++]);
		Links.m_staff_id = GetSQLLong(row[nRow++]);
		Links.m_date_in.ParseDateTime( GetSQLString(row[nRow++]) );
		Links.m_uid = GetSQLString(row[nRow++]);
		Links.m_title = GetSQLString(row[nRow++]);
		Links.m_source = GetSQLString(row[nRow++]);
		Links.m_link = GetSQLString(row[nRow++]);
		Links.m_notes = GetSQLString(row[nRow++]);
		Links.m_material = GetSQLInt(row[nRow++]);
		// insert
		pLinks->Add( Links );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetRequestLinkss(CArray<REQUEST_LINK> *pLinks, CString szFilter, CString szSort)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, staff_id, date_in, uid, title, source, link, notes, material FROM request_links") );

	// add filter
	if( !szFilter.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE %s"), szQuery, szFilter );
		szQuery = szTemp;
	}

	// add sort
	if( !szSort.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s ORDER BY %s"), szQuery, szSort );
		szQuery = szTemp;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	REQUEST_LINK Links;
	// clear array
	pLinks->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		Links.m_id = GetSQLLong(row[nRow++]);
		Links.m_staff_id = GetSQLLong(row[nRow++]);
		Links.m_date_in.ParseDateTime( GetSQLString(row[nRow++]) );
		Links.m_uid = GetSQLString(row[nRow++]);
		Links.m_title = GetSQLString(row[nRow++]);
		Links.m_source = GetSQLString(row[nRow++]);
		Links.m_link = GetSQLString(row[nRow++]);
		Links.m_notes = GetSQLString(row[nRow++]);
		Links.m_material = GetSQLInt(row[nRow++]);
		// insert
		pLinks->Add( Links );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::SetRequestLinks(REQUEST_LINK *pLinks, LONG nID, BOOL bInsert)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	MYSQL_RES *res;
	MYSQL_ROW row;

	// get current date
	COleDateTime time = COleDateTime::GetCurrentTime();
	pLinks->m_date_in = time;

	// check for existing
	if( bInsert )
	{
		ULONG nCount = 0;

		// check ID
		szQuery.Format( _T("SELECT id FROM request_links WHERE id=%ld"), nID );
		res = QueryForRow( szQuery, &row );
		if( res && res->row_count == 1 )
		{
			INT nRow = 0;
			pLinks->m_id = GetSQLLong(row[nRow++]);
			nCount += (ULONG)mysql_num_rows( res );
		}
		mysql_free_result( res );

		// check UID
		szQuery.Format( _T("SELECT id FROM request_links WHERE uid='%s'"), ES(pLinks->m_uid) );
		res = QueryForRow( szQuery, &row );
		if( res && res->row_count == 1 )
		{
			INT nRow = 0;
			pLinks->m_id = GetSQLLong(row[nRow++]);
			nCount += (ULONG)mysql_num_rows( res );
		}
		mysql_free_result( res );

		if( nCount <= 0 )
		{
			szQuery.Format( _T("INSERT INTO request_links (staff_id, date_in, uid, title, source, link, notes, material) VALUES (%ld, '%s', '%s', '%s', '%s', '%s', '%s', %d)"),
				pLinks->m_staff_id, pLinks->m_date_in.Format( _T("%Y-%m-%d %H:%M:%S")), ES(pLinks->m_uid), ES(pLinks->m_title), ES(pLinks->m_source), ES(pLinks->m_link),
				ES(pLinks->m_notes), pLinks->m_material );
		}
		else
		{
			bInsert = FALSE;
		}
	}

	// must be a UPDATE
	if( !bInsert )
	{
		szQuery.Format( _T("UPDATE request_links SET staff_id=%ld, date_in='%s', uid='%s', title='%s', source='%s', link='%s', notes='%s', material=%d"),
			pLinks->m_staff_id, pLinks->m_date_in.Format( _T("%Y-%m-%d %H:%M:%S")), ES(pLinks->m_uid), ES(pLinks->m_title), ES(pLinks->m_source), ES(pLinks->m_link),
			pLinks->m_notes, pLinks->m_material );

		// set ID
		CString szWhere;
		if( nID >= 0 )
			szWhere.Format( _T(" WHERE id=%ld"), nID );
		else
			szWhere.Format( _T(" WHERE id=%ld"), pLinks->m_id );

		// add where
		szQuery += szWhere;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetSourceLinkSources(CStringArray *pSources)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT source FROM source_links") );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// clear array
	pSources->RemoveAll();

	CString szSource;

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		szSource = GetSQLString(row[0]);
		// insert
		pSources->Add( szSource );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetSourceLink(SOURCE_LINK *pLink, LONG nID)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, staff_id, date_in, title, link, notes, material FROM source_links WHERE id=%ld"), nID );

	// get row
	res = QueryForRow( szQuery, &row );

	BOOL bRetVal = TRUE;
	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		pLink->m_id = GetSQLLong(row[nRow++]);
		pLink->m_staff_id = GetSQLLong(row[nRow++]);
		pLink->m_date_in.ParseDateTime( GetSQLString(row[nRow++]) );
		pLink->m_title = GetSQLString(row[nRow++]);
		pLink->m_link = GetSQLString(row[nRow++]);
		pLink->m_notes = GetSQLString(row[nRow++]);
		pLink->m_material = GetSQLInt(row[nRow++]);
	}
	else
	{
		pLink->m_id = -1;
		pLink->m_staff_id = -1;
		pLink->m_title = _T("");
		pLink->m_link = _T("");
		pLink->m_notes = _T("");
		pLink->m_material = 0;
		bRetVal = FALSE;
	}
	mysql_free_result( res );

	Close();
	return bRetVal;
}

BOOL CMySQLEx::GetSourceLink(SOURCE_LINK *pLink, CString szField, CString szFilter)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, staff_id, date_in, title, link, notes, material FROM source_links WHERE %s='%s'"), szField, szFilter );

	// get row
	res = QueryForRow( szQuery, &row );

	BOOL bRetVal = TRUE;
	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		pLink->m_id = GetSQLLong(row[nRow++]);
		pLink->m_staff_id = GetSQLLong(row[nRow++]);
		pLink->m_date_in.ParseDateTime( GetSQLString(row[nRow++]) );
		pLink->m_title = GetSQLString(row[nRow++]);
		pLink->m_link = GetSQLString(row[nRow++]);
		pLink->m_notes = GetSQLString(row[nRow++]);
		pLink->m_material = GetSQLInt(row[nRow++]);
	}
	else
	{
		pLink->m_id = -1;
		pLink->m_staff_id = -1;
		pLink->m_title = _T("");
		pLink->m_link = _T("");
		pLink->m_notes = _T("");
		pLink->m_material = 0;
		bRetVal = FALSE;
	}
	// release results
	mysql_free_result( res );

	Close();
	return bRetVal;
}

BOOL CMySQLEx::GetSourceLinks(CArray<SOURCE_LINK> *pLinks, CString szFilter, CString szSort)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, staff_id, date_in, title, link, notes, material FROM source_links") );

	// add filter
	if( !szFilter.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE %s"), szQuery, szFilter );
		szQuery = szTemp;
	}

	// add sort
	if( !szSort.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s ORDER BY %s"), szQuery, szSort );
		szQuery = szTemp;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	SOURCE_LINK Links;
	// clear array
	pLinks->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		Links.m_id = GetSQLLong(row[nRow++]);
		Links.m_staff_id = GetSQLLong(row[nRow++]);
		Links.m_date_in.ParseDateTime( GetSQLString(row[nRow++]) );
		Links.m_title = GetSQLString(row[nRow++]);
		Links.m_link = GetSQLString(row[nRow++]);
		Links.m_notes = GetSQLString(row[nRow++]);
		Links.m_material = GetSQLInt(row[nRow++]);
		// insert
		pLinks->Add( Links );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::SetSourceLink(SOURCE_LINK *pLink, LONG nID, BOOL bInsert)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	MYSQL_RES *res;
	MYSQL_ROW row;

	// get current date
	COleDateTime time = COleDateTime::GetCurrentTime();
	pLink->m_date_in = time;

	// check for existing
	if( bInsert )
	{
		ULONG nCount = 0;

		// check ID
		szQuery.Format( _T("SELECT id FROM source_links WHERE id=%ld"), nID );
		res = QueryForRow( szQuery, &row );
		if( res && res->row_count == 1 )
		{
			INT nRow = 0;
			pLink->m_id = GetSQLLong(row[nRow++]);
			nCount += (ULONG)mysql_num_rows( res );
		}
		mysql_free_result( res );

		// check title
		szQuery.Format( _T("SELECT id FROM source_links WHERE title='%s'"), pLink->m_title );
		res = QueryForRow( szQuery, &row );
		if( res && res->row_count == 1 )
		{
			INT nRow = 0;
			pLink->m_id = GetSQLLong(row[nRow++]);
			nCount += (ULONG)mysql_num_rows( res );
		}
		mysql_free_result( res );

		if( nCount <= 0 )
		{
			szQuery.Format( _T("INSERT INTO source_links (staff_id, date_in, title, link, notes, material) VALUES (%ld, '%s', '%s', '%s', '%s', %d)"),
				pLink->m_staff_id, pLink->m_date_in.Format( _T("%Y-%m-%d %H:%M:%S")), ES(pLink->m_title), ES(pLink->m_link),
				ES(pLink->m_notes), pLink->m_material );
		}
		else
		{
			bInsert = FALSE;
		}
	}

	// must be a UPDATE
	if( !bInsert )
	{
		szQuery.Format( _T("UPDATE source_links SET staff_id=%ld, date_in='%s', title='%s', link='%s', notes='%s', material=%d"),
			pLink->m_staff_id, pLink->m_date_in.Format( _T("%Y-%m-%d %H:%M:%S")), ES(pLink->m_title), ES(pLink->m_link),
			ES(pLink->m_notes), pLink->m_material );

		// set ID
		CString szWhere;
		if( nID >= 0 )
			szWhere.Format( _T(" WHERE id=%ld"), nID );
		else
			szWhere.Format( _T(" WHERE id=%ld"), pLink->m_id );

		// add where
		szQuery += szWhere;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	// get inserted ID
	if( bInsert )
	{
		nID = (LONG)mysql_insert_id( &m_mysql );
		GetSourceLink( pLink, nID );
	}

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetDescriptionCollection(COLLECTION_LOOKUP *pCollection, LONG nID)
{
	USES_CONVERSION;

	ASSERT( nID >= 0 );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, description, name, user_listed, electronic, icon_file, print_notice FROM description_collection WHERE id=%ld"), nID );

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		pCollection->nID = GetSQLLong(row[nRow++]);
		pCollection->szDescription = GetSQLString(row[nRow++]);
		pCollection->szName = GetSQLString(row[nRow++]);
		pCollection->nUserListed = GetSQLInt(row[nRow++]);
		pCollection->nElectronic = GetSQLInt(row[nRow++]);
		pCollection->szIconFile = GetSQLString(row[nRow++]);
		pCollection->szPrintNotice = GetSQLString(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetDescriptionCollections(CArray<COLLECTION_LOOKUP> *pCollection, LPCTSTR lpszSort)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, description, name, user_listed, electronic, icon_file, print_notice FROM description_collection") );

	// add sort
	if( lpszSort != NULL )
	{
		if( _tcslen( lpszSort ) > 0 )
		{
			CString szTemp;
			szTemp.Format( _T("%s ORDER BY %s"), szQuery, lpszSort );
			szQuery = szTemp;
		}
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	COLLECTION_LOOKUP Collection;
	// clear array
	pCollection->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		Collection.nID = GetSQLLong(row[nRow++]);
		Collection.szDescription = GetSQLString(row[nRow++]);
		Collection.szName = GetSQLString(row[nRow++]);
		Collection.nUserListed = GetSQLInt(row[nRow++]);
		Collection.nElectronic = GetSQLInt(row[nRow++]);
		Collection.szIconFile = GetSQLString(row[nRow++]);
		Collection.szPrintNotice = GetSQLString(row[nRow++]);
		// insert
		pCollection->SetAtGrow( Collection.nID, Collection );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::SetDescriptionCollection(COLLECTION_LOOKUP *pCollection, LONG nID, BOOL bInsert)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	MYSQL_RES *res;
	MYSQL_ROW row;

	// check for existing
	if( bInsert )
	{
		szQuery.Format( _T("SELECT id FROM description_collection WHERE id=%ld"), nID );
		res = QueryForRow( szQuery, &row );
		ULONG nCount = (ULONG)mysql_num_rows( res );
		mysql_free_result( res );

		if( nCount <= 0 )
		{
			szQuery.Format( _T("INSERT INTO description_collection (id, description, name, user_listed, electronic, icon_file, print_notice) VALUES (%ld, '%s', '%s', %d, %d, '%s', '%s')"),
				nID, ES(pCollection->szDescription), ES(pCollection->szName), pCollection->nUserListed, pCollection->nElectronic, ES(pCollection->szIconFile), ES(pCollection->szPrintNotice) );
		}
		else
		{
			bInsert = FALSE;
		}
	}

	// must be a UPDATE
	if( !bInsert )
	{
		szQuery.Format( _T("UPDATE description_collection SET description='%s',name='%s',user_listed=%d,electronic=%d,icon_file='%s',print_notice='%s'"),
			ES(pCollection->szDescription), ES(pCollection->szName), pCollection->nUserListed, pCollection->nElectronic, ES(pCollection->szIconFile), ES(pCollection->szPrintNotice) );

		// set ID
		CString szWhere;
		if( nID >= 0 )
			szWhere.Format( _T(" WHERE id=%ld"), nID );
		else
			szWhere.Format( _T(" WHERE id=%ld"), pCollection->nID );

		// add where
		szQuery += szWhere;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetDescriptionUserType(USER_TYPE_LOOKUP *pUserType, LONG nID)
{
	USES_CONVERSION;

	ASSERT( nID >= 0 );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, description, external FROM description_user_type WHERE id=%ld"), nID );

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		pUserType->nID = GetSQLLong(row[nRow++]);
		pUserType->szDescription = GetSQLString(row[nRow++]);
		pUserType->nExternal = GetSQLInt(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::SetDescriptionUserType(USER_TYPE_LOOKUP *pUserType, LONG nID, BOOL bInsert)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	MYSQL_RES *res;
	MYSQL_ROW row;

	// check for existing
	if( bInsert )
	{
		szQuery.Format( _T("SELECT id FROM description_user_type WHERE id=%ld"), nID );
		res = QueryForRow( szQuery, &row );
		ULONG nCount = (ULONG)mysql_num_rows( res );
		mysql_free_result( res );

		if( nCount <= 0 )
		{
			szQuery.Format( _T("INSERT INTO description_user_type (id, description, external) VALUES (%ld, '%s', %d)"),
				nID, ES(pUserType->szDescription), pUserType->nExternal );
		}
		else
		{
			bInsert = FALSE;
		}
	}

	// must be a UPDATE
	if( !bInsert )
	{
		szQuery.Format( _T("UPDATE description_user_type SET description='%s',external=%d"),
			ES(pUserType->szDescription), pUserType->nExternal );

		// set ID
		CString szWhere;
		if( nID >= 0 )
			szWhere.Format( _T(" WHERE id=%ld"), nID );
		else
			szWhere.Format( _T(" WHERE id=%ld"), pUserType->nID );

		// add where
		szQuery += szWhere;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetDescriptionILL(ILL_LOOKUP *pILL, LONG nID)
{
	USES_CONVERSION;

	ASSERT( nID >= 0 );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, description, explanation, message_subject, user_listed \
					   FROM description_ill WHERE id=%ld"), nID );

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		pILL->nID = GetSQLLong(row[nRow++]);
		pILL->szDescription = GetSQLString(row[nRow++]);
		pILL->szExplanation = GetSQLString(row[nRow++]);
		pILL->szMessageSubject = GetSQLString(row[nRow++]);
		pILL->nUserListed = GetSQLInt(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetDescriptionILLs(CArray<ILL_LOOKUP> *pILL, LPCTSTR lpszSort)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, description, explanation, message_subject, user_listed \
					   FROM description_ill") );

	// add sort
	if( lpszSort != NULL )
	{
		if( _tcslen( lpszSort ) > 0 )
		{
			CString szTemp;
			szTemp.Format( _T("%s ORDER BY %s"), szQuery, lpszSort );
			szQuery = szTemp;
		}
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	ILL_LOOKUP ILL;
	// clear array
	pILL->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		ILL.nID = GetSQLLong(row[nRow++]);
		ILL.szDescription = GetSQLString(row[nRow++]);
		ILL.szExplanation = GetSQLString(row[nRow++]);
		ILL.szMessageSubject = GetSQLString(row[nRow++]);
		ILL.nUserListed = GetSQLInt(row[nRow++]);
		// insert
		pILL->SetAtGrow( ILL.nID, ILL );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::SetDescriptionILL(ILL_LOOKUP *pILL, LONG nID, BOOL bInsert)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	MYSQL_RES *res;
	MYSQL_ROW row;

	// check for existing
	if( bInsert )
	{
		szQuery.Format( _T("SELECT id FROM description_ill WHERE id=%ld"), nID );
		res = QueryForRow( szQuery, &row );
		ULONG nCount = (ULONG)mysql_num_rows( res );
		mysql_free_result( res );

		if( nCount <= 0 )
		{
			szQuery.Format( _T("INSERT INTO description_ill (id, description, explanation, message_subject, user_listed) \
							   VALUES (%ld, '%s', '%s', '%s', %d)"),
							   nID, ES(pILL->szDescription), ES(pILL->szExplanation), ES(pILL->szMessageSubject), pILL->nUserListed );
		}
		else
		{
			bInsert = FALSE;
		}
	}

	// must be a UPDATE
	if( !bInsert )
	{
		szQuery.Format( _T("UPDATE description_ill SET description='%s', explanation='%s', message_subject='%s', user_listed=%d"),
						   ES(pILL->szDescription), ES(pILL->szExplanation), ES(pILL->szMessageSubject), pILL->nUserListed );

		// set ID
		CString szWhere;
		if( nID >= 0 )
			szWhere.Format( _T(" WHERE id=%ld"), nID );
		else
			szWhere.Format( _T(" WHERE id=%ld"), pILL->nID );

		// add where
		szQuery += szWhere;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetDescriptionMethod(METHOD_LOOKUP *pMethod, LONG nID)
{
	USES_CONVERSION;

	ASSERT( nID >= 0 );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, description, print_notice, icon_file, fee_service, user_listed, electronic, message_file, message_subject, information \
					   FROM description_method WHERE id=%ld"), nID );

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		pMethod->nID = GetSQLLong(row[nRow++]);
		pMethod->szDescription = GetSQLString(row[nRow++]);
		pMethod->szPrintNotice = GetSQLString(row[nRow++]);
		pMethod->szIconFile = GetSQLString(row[nRow++]);
		pMethod->dServiceFee = GetSQLDouble(row[nRow++]);
		pMethod->nUserListed = GetSQLInt(row[nRow++]);
		pMethod->nElectronic = GetSQLInt(row[nRow++]);
		pMethod->szMessageFile = GetSQLString(row[nRow++]);
		pMethod->szMessageSubject = GetSQLString(row[nRow++]);
		pMethod->szInformation = GetSQLString(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetDescriptionMethods(CArray<METHOD_LOOKUP> *pMethod, LPCTSTR lpszSort)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, description, print_notice, icon_file, fee_service, user_listed, electronic, message_file, message_subject, information \
					   FROM description_method") );

	// add sort
	if( lpszSort != NULL )
	{
		if( _tcslen( lpszSort ) > 0 )
		{
			CString szTemp;
			szTemp.Format( _T("%s ORDER BY %s"), szQuery, lpszSort );
			szQuery = szTemp;
		}
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	METHOD_LOOKUP Method;
	// clear array
	pMethod->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		Method.nID = GetSQLLong(row[nRow++]);
		Method.szDescription = GetSQLString(row[nRow++]);
		Method.szPrintNotice = GetSQLString(row[nRow++]);
		Method.szIconFile = GetSQLString(row[nRow++]);
		Method.dServiceFee = GetSQLDouble(row[nRow++]);
		Method.nUserListed = GetSQLInt(row[nRow++]);
		Method.nElectronic = GetSQLInt(row[nRow++]);
		Method.szMessageFile = GetSQLString(row[nRow++]);
		Method.szMessageSubject = GetSQLString(row[nRow++]);
		Method.szInformation = GetSQLString(row[nRow++]);
		// insert
		pMethod->SetAtGrow( Method.nID, Method );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::SetDescriptionMethod(METHOD_LOOKUP *pMethod, LONG nID, BOOL bInsert)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	MYSQL_RES *res;
	MYSQL_ROW row;

	// check for existing
	if( bInsert )
	{
		szQuery.Format( _T("SELECT id FROM description_method WHERE id=%ld"), nID );
		res = QueryForRow( szQuery, &row );
		ULONG nCount = (ULONG)mysql_num_rows( res );
		mysql_free_result( res );

		if( nCount <= 0 )
		{
			szQuery.Format( _T("INSERT INTO description_method (id, description, print_notice, icon_file, fee_service, user_listed, electronic, \
							   message_file, message_subject, information) \
							   VALUES (%ld, '%s', '%s', '%s', %f, %d, %d, '%s', '%s', '%s')"),
							   nID, ES(pMethod->szDescription), ES(pMethod->szPrintNotice), ES(pMethod->szIconFile), pMethod->dServiceFee, pMethod->nUserListed, pMethod->nElectronic,
							   ES(pMethod->szMessageFile), ES(pMethod->szMessageSubject), ES(pMethod->szInformation) );
		}
		else
		{
			bInsert = FALSE;
		}
	}

	// must be a UPDATE
	if( !bInsert )
	{
		szQuery.Format( _T("UPDATE description_method SET description='%s', print_notice='%s', icon_file='%s', fee_service=%f, user_listed=%d, electronic=%d, \
						   message_file='%s', message_subject='%s', information='%s'"),
						   ES(pMethod->szDescription), ES(pMethod->szPrintNotice), ES(pMethod->szIconFile), pMethod->dServiceFee, pMethod->nUserListed, pMethod->nElectronic,
						   ES(pMethod->szMessageFile), ES(pMethod->szMessageSubject), ES(pMethod->szInformation) );

		// set ID
		CString szWhere;
		if( nID >= 0 )
			szWhere.Format( _T(" WHERE id=%ld"), nID );
		else
			szWhere.Format( _T(" WHERE id=%ld"), pMethod->nID );

		// add where
		szQuery += szWhere;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetDescriptionPriority(PRIORITY_LOOKUP *pPriority, LONG nID)
{
	USES_CONVERSION;

	ASSERT( nID >= 0 );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, description, print_notice, icon_file, fee_service, in_hours, user_listed, color_text, \
					   color_text_old, color_background, color_background_old FROM description_priority WHERE id=%ld"), nID );

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		pPriority->nID = GetSQLLong(row[nRow++]);
		pPriority->szDescription = GetSQLString(row[nRow++]);
		pPriority->szPrintNotice = GetSQLString(row[nRow++]);
		pPriority->szIconFile = GetSQLString(row[nRow++]);
		pPriority->dServiceFee = GetSQLDouble(row[nRow++]);
		pPriority->nInHours = GetSQLLong(row[nRow++]);
		pPriority->nUserListed = GetSQLLong(row[nRow++]);
		pPriority->szTextColor = GetSQLString(row[nRow++]);
		pPriority->szTextColorOld = GetSQLString(row[nRow++]);
		pPriority->szBackGroundColor = GetSQLString(row[nRow++]);
		pPriority->szBackGroundColorOld = GetSQLString(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetDescriptionPrioritys(CArray<PRIORITY_LOOKUP> *pPriority, LPCTSTR lpszSort)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, description, print_notice, icon_file, fee_service, in_hours, user_listed, color_text, \
					   color_text_old, color_background, color_background_old FROM description_priority") );

	// add sort
	if( lpszSort != NULL )
	{
		if( _tcslen( lpszSort ) > 0 )
		{
			CString szTemp;
			szTemp.Format( _T("%s ORDER BY %s"), szQuery, lpszSort );
			szQuery = szTemp;
		}
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	PRIORITY_LOOKUP Priority;
	// clear array
	pPriority->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		Priority.nID = GetSQLLong(row[nRow++]);
		Priority.szDescription = GetSQLString(row[nRow++]);
		Priority.szPrintNotice = GetSQLString(row[nRow++]);
		Priority.szIconFile = GetSQLString(row[nRow++]);
		Priority.dServiceFee = GetSQLDouble(row[nRow++]);
		Priority.nInHours = GetSQLLong(row[nRow++]);
		Priority.nUserListed = GetSQLLong(row[nRow++]);
		Priority.szTextColor = GetSQLString(row[nRow++]);
		Priority.szTextColorOld = GetSQLString(row[nRow++]);
		Priority.szBackGroundColor = GetSQLString(row[nRow++]);
		Priority.szBackGroundColorOld = GetSQLString(row[nRow++]);
		// insert
		pPriority->SetAtGrow( Priority.nID, Priority );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::SetDescriptionPriority(PRIORITY_LOOKUP *pPriority, LONG nID, BOOL bInsert)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	MYSQL_RES *res;
	MYSQL_ROW row;

	// check for existing
	if( bInsert )
	{
		szQuery.Format( _T("SELECT id FROM description_priority WHERE id=%ld"), nID );
		res = QueryForRow( szQuery, &row );
		ULONG nCount = (ULONG)mysql_num_rows( res );
		mysql_free_result( res );

		if( nCount <= 0 )
		{
			szQuery.Format( _T("INSERT INTO description_priority (id, description, print_notice, icon_file, fee_service, in_hours, user_listed, color_text, color_text_old, color_background, color_background_old) \
							   VALUES (%ld, '%s', '%s', '%s', %f, %d, %d, '%s', '%s', '%s', '%s')"),
				nID, ES(pPriority->szDescription), ES(pPriority->szPrintNotice), ES(pPriority->szIconFile), pPriority->dServiceFee, pPriority->nInHours, pPriority->nUserListed, ES(pPriority->szTextColor), ES(pPriority->szTextColorOld), ES(pPriority->szBackGroundColor), ES(pPriority->szBackGroundColorOld) );
		}
		else
		{
			bInsert = FALSE;
		}
	}

	// must be a UPDATE
	if( !bInsert )
	{
		szQuery.Format( _T("UPDATE description_priority SET description='%s', print_notice='%s', icon_file='%s', fee_service=%f, in_hours=%d, user_listed=%d, color_text='%s', color_text_old='%s', color_background='%s', color_background_old='%s'"),
			ES(pPriority->szDescription), ES(pPriority->szPrintNotice), ES(pPriority->szIconFile), pPriority->dServiceFee, pPriority->nInHours, pPriority->nUserListed, ES(pPriority->szTextColor), ES(pPriority->szTextColorOld), ES(pPriority->szBackGroundColor), ES(pPriority->szBackGroundColorOld) );

		// set ID
		CString szWhere;
		if( nID >= 0 )
			szWhere.Format( _T(" WHERE id=%ld"), nID );
		else
			szWhere.Format( _T(" WHERE id=%ld"), pPriority->nID );

		// add where
		szQuery += szWhere;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetDescriptionStatus(STATUS_LOOKUP *pStatus, LONG nID)
{
	USES_CONVERSION;

	ASSERT( nID >= 0 );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	//id, description, explanation, message, code, icon_file, notify, send_ill

	CString szQuery;
	szQuery.Format( _T("SELECT id, description, explanation, subject, message, code, icon_file, notify, send_ill, complete, information, \
					   message_file, message_file_ext \
					   FROM description_status WHERE id=%ld"), nID );

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		pStatus->nID = GetSQLLong(row[nRow++]);
		pStatus->szDescription = GetSQLString(row[nRow++]);
		pStatus->szExplanation = GetSQLString(row[nRow++]);
		pStatus->szSubject = GetSQLString(row[nRow++]);
		pStatus->szMessage = GetSQLString(row[nRow++]);
		pStatus->szCode = GetSQLString(row[nRow++]);
		pStatus->szIconFile = GetSQLString(row[nRow++]);
		pStatus->szNotify = GetSQLString(row[nRow++]);
		pStatus->nSendILL = GetSQLInt(row[nRow++]);
		pStatus->nComplete = GetSQLInt(row[nRow++]);
		pStatus->szInformation = GetSQLString(row[nRow++]);
		pStatus->szMessageFile = GetSQLString(row[nRow++]);
		pStatus->szMessageFileExt = GetSQLString(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetDescriptionStatuss(CArray<STATUS_LOOKUP> *pStatus, LPCTSTR lpszSort)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, description, explanation, subject, message, code, icon_file, notify, send_ill, complete, information, \
					   message_file, message_file_ext \
					   FROM description_status") );

	// add sort
	if( lpszSort != NULL )
	{
		if( _tcslen( lpszSort ) > 0 )
		{
			CString szTemp;
			szTemp.Format( _T("%s ORDER BY %s"), szQuery, lpszSort );
			szQuery = szTemp;
		}
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	STATUS_LOOKUP Status;
	// clear array
	pStatus->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		Status.nID = GetSQLLong(row[nRow++]);
		Status.szDescription = GetSQLString(row[nRow++]);
		Status.szExplanation = GetSQLString(row[nRow++]);
		Status.szSubject = GetSQLString(row[nRow++]);
		Status.szMessage = GetSQLString(row[nRow++]);
		Status.szCode = GetSQLString(row[nRow++]);
		Status.szIconFile = GetSQLString(row[nRow++]);
		Status.szNotify = GetSQLString(row[nRow++]);
		Status.nSendILL = GetSQLInt(row[nRow++]);
		Status.nComplete = GetSQLInt(row[nRow++]);
		Status.szInformation = GetSQLString(row[nRow++]);
		Status.szMessageFile = GetSQLString(row[nRow++]);
		Status.szMessageFileExt = GetSQLString(row[nRow++]);
		// insert
		pStatus->SetAtGrow( Status.nID, Status );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::SetDescriptionStatus(STATUS_LOOKUP *pStatus, LONG nID, BOOL bInsert)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	MYSQL_RES *res;
	MYSQL_ROW row;

	// check for existing
	if( bInsert )
	{
		szQuery.Format( _T("SELECT id FROM description_status WHERE id=%ld"), nID );
		res = QueryForRow( szQuery, &row );
		ULONG nCount = (ULONG)mysql_num_rows( res );
		mysql_free_result( res );

		if( nCount <= 0 )
		{
			szQuery.Format( _T("INSERT INTO description_status (id, description, explanation, subject, message, code, icon_file, notify, send_ill, complete, \
							   information, message_file, message_file_ext) \
							   VALUES (%ld, '%s', '%s', '%s', '%s', '%s', '%s', '%s', %d, %d, \
							   '%s', '%s', '%s')"),
							   nID, ES(pStatus->szDescription), ES(pStatus->szExplanation), ES(pStatus->szSubject), ES(pStatus->szMessage), ES(pStatus->szCode), ES(pStatus->szIconFile), ES(pStatus->szNotify),
							   pStatus->nSendILL, pStatus->nComplete, ES(pStatus->szInformation), ES(pStatus->szMessageFile), ES(pStatus->szMessageFileExt) );
		}
		else
		{
			bInsert = FALSE;
		}
	}

	// must be a UPDATE
	if( !bInsert )
	{
		szQuery.Format( _T("UPDATE description_status SET description='%s', explanation='%s', subject='%s', message='%s', code='%s', icon_file='%s', notify='%s', \
						   send_ill=%d, complete=%d, information='%s', message_file='%s', message_file_ext='%s'"),
						   ES(pStatus->szDescription), ES(pStatus->szExplanation), ES(pStatus->szSubject), ES(pStatus->szMessage), ES(pStatus->szCode), ES(pStatus->szIconFile), ES(pStatus->szNotify),
						   pStatus->nSendILL, pStatus->nComplete, ES(pStatus->szInformation), ES(pStatus->szMessageFile), ES(pStatus->szMessageFileExt) );

		// set ID
		CString szWhere;
		if( nID >= 0 )
			szWhere.Format( _T(" WHERE id=%ld"), nID );
		else
			szWhere.Format( _T(" WHERE id=%ld"), pStatus->nID );

		// add where
		szQuery += szWhere;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetDescriptionLibrary(LIBRARY_LOOKUP *pLibrary, LONG nID)
{
	USES_CONVERSION;

	ASSERT( nID >= 0 );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, description, name, phone, address, email, subject, type, user_listed, information, diverted\
					   FROM description_library WHERE id=%ld"), nID );

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		pLibrary->nID = GetSQLLong(row[nRow++]);
		pLibrary->szDescription = GetSQLString(row[nRow++]);
		pLibrary->szName = GetSQLString(row[nRow++]);
		pLibrary->szPhone = GetSQLString(row[nRow++]);
		pLibrary->szAddress = GetSQLString(row[nRow++]);
		pLibrary->szEmail = GetSQLString(row[nRow++]);
		pLibrary->szSubject = GetSQLString(row[nRow++]);
		pLibrary->nType = GetSQLLong(row[nRow++]);
		pLibrary->nUserListed = GetSQLLong(row[nRow++]);
		pLibrary->szInformation = GetSQLString(row[nRow++]);
		pLibrary->bDiverted = GetSQLInt(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetDescriptionLibrarys(CArray<LIBRARY_LOOKUP> *pLibrary, LPCTSTR lpszSort)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, description, name, phone, address, email, subject, type, user_listed, information, diverted\
					   FROM description_library") );

	// add sort
	if( lpszSort != NULL )
	{
		if( _tcslen( lpszSort ) > 0 )
		{
			CString szTemp;
			szTemp.Format( _T("%s ORDER BY %s"), szQuery, lpszSort );
			szQuery = szTemp;
		}
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	LIBRARY_LOOKUP Library;
	// clear array
	pLibrary->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		Library.nID = GetSQLLong(row[nRow++]);
		Library.szDescription = GetSQLString(row[nRow++]);
		Library.szName = GetSQLString(row[nRow++]);
		Library.szPhone = GetSQLString(row[nRow++]);
		Library.szAddress = GetSQLString(row[nRow++]);
		Library.szEmail = GetSQLString(row[nRow++]);
		Library.szSubject = GetSQLString(row[nRow++]);
		Library.nType = GetSQLLong(row[nRow++]);
		Library.nUserListed = GetSQLLong(row[nRow++]);
		Library.szInformation = GetSQLString(row[nRow++]);
		Library.bDiverted = GetSQLInt(row[nRow++]);
		// insert
		pLibrary->SetAtGrow( Library.nID, Library );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetDescriptionLibraryList(CArray<LIBRARY_LOOKUP> *pLibrary, LPCTSTR lpszSort)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, description, name, phone, address, email, subject, type, user_listed, information, diverted\
					   FROM description_library") );

	// add sort
	if( lpszSort != NULL )
	{
		if( _tcslen( lpszSort ) > 0 )
		{
			CString szTemp;
			szTemp.Format( _T("%s ORDER BY %s"), szQuery, lpszSort );
			szQuery = szTemp;
		}
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	LIBRARY_LOOKUP Library;
	// clear array
	pLibrary->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		Library.nID = GetSQLLong(row[nRow++]);
		Library.szDescription = GetSQLString(row[nRow++]);
		Library.szName = GetSQLString(row[nRow++]);
		Library.szPhone = GetSQLString(row[nRow++]);
		Library.szAddress = GetSQLString(row[nRow++]);
		Library.szEmail = GetSQLString(row[nRow++]);
		Library.szSubject = GetSQLString(row[nRow++]);
		Library.nType = GetSQLLong(row[nRow++]);
		Library.nUserListed = GetSQLLong(row[nRow++]);
		Library.szInformation = GetSQLString(row[nRow++]);
		Library.bDiverted = GetSQLInt(row[nRow++]);
		// insert
		pLibrary->Add( Library );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::SetDescriptionLibrary(LIBRARY_LOOKUP *pLibrary, LONG nID, BOOL bInsert)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	MYSQL_RES *res;
	MYSQL_ROW row;

	// check for existing
	if( bInsert )
	{
		szQuery.Format( _T("SELECT id FROM description_library WHERE id=%ld"), nID );
		res = QueryForRow( szQuery, &row );
		ULONG nCount = (ULONG)mysql_num_rows( res );
		mysql_free_result( res );

		if( nCount <= 0 )
		{
			szQuery.Format( _T("INSERT INTO description_library (id, description, name, phone, address, email, subject,\
							   type, user_listed, information, diverted) \
							   VALUES (%ld, '%s', '%s', '%s', '%s', '%s', '%s',\
							   %d, %d, '%s', %d)"),
							   nID, ES(pLibrary->szDescription), ES(pLibrary->szName), ES(pLibrary->szPhone), ES(pLibrary->szAddress), ES(pLibrary->szEmail), ES(pLibrary->szSubject),
							   pLibrary->nType, pLibrary->nUserListed, pLibrary->szInformation, pLibrary->bDiverted );
		}
		else
		{
			bInsert = FALSE;
		}
	}

	// must be a UPDATE
	if( !bInsert )
	{
		szQuery.Format( _T("UPDATE description_library SET description='%s', name='%s', phone='%s', address='%s', email='%s', subject='%s',\
						   type=%d, user_listed=%d, information='%s', diverted=%d"),
						   ES(pLibrary->szDescription), ES(pLibrary->szName), ES(pLibrary->szPhone), ES(pLibrary->szAddress), ES(pLibrary->szEmail), ES(pLibrary->szSubject),
						   pLibrary->nType, pLibrary->nUserListed, pLibrary->szInformation, pLibrary->bDiverted );

		// set ID
		CString szWhere;
		if( nID >= 0 )
			szWhere.Format( _T(" WHERE id=%ld"), nID );
		else
			szWhere.Format( _T(" WHERE id=%ld"), pLibrary->nID );

		// add where
		szQuery += szWhere;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetDescriptionStaff(STAFF_LOOKUP *pStaff, LONGLONG nID)
{
	USES_CONVERSION;

	ASSERT( nID >= 0 );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, level, name, email, phone, library_id FROM staff WHERE id=%I64d"), nID );

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		pStaff->nStaffID = GetSQLLong(row[nRow++]);
		pStaff->nStaffLevel = GetSQLLong(row[nRow++]);
		pStaff->szStaffName = GetSQLString(row[nRow++]);
		pStaff->szStaffEmail = GetSQLString(row[nRow++]);
		pStaff->szStaffPhone = GetSQLString(row[nRow++]);
		pStaff->nLibraryID = GetSQLLong(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetDescriptionStaffs(CArray<STAFF_LOOKUP> *pStaff, LPCTSTR lpszSort, LPCTSTR lpszFilter, BOOL bSetAt)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, level, name, email, phone, library_id FROM staff") );

	// add filter
	if( lpszFilter != NULL )
	{
		if( _tcslen( lpszFilter ) > 0 )
		{
			CString szTemp;
			szTemp.Format( _T("%s WHERE %s"), szQuery, lpszFilter );
			szQuery = szTemp;
		}
	}

	// add sort
	if( lpszSort != NULL )
	{
		if( _tcslen( lpszSort ) > 0 )
		{
			CString szTemp;
			szTemp.Format( _T("%s ORDER BY %s"), szQuery, lpszSort );
			szQuery = szTemp;
		}
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	STAFF_LOOKUP Staff;
	// clear array
	pStaff->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		Staff.nStaffID = GetSQLLong(row[nRow++]);
		Staff.nStaffLevel = GetSQLLong(row[nRow++]);
		Staff.szStaffName = GetSQLString(row[nRow++]);
		Staff.szStaffEmail = GetSQLString(row[nRow++]);
		Staff.szStaffPhone = GetSQLString(row[nRow++]);
		Staff.nLibraryID = GetSQLLong(row[nRow++]);
		// insert
		if( bSetAt )
			pStaff->SetAtGrow( Staff.nStaffID, Staff );
		else
			pStaff->Add( Staff );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetStaffSet(STAFF_SET *pStaff, LONGLONG nID)
{
	USES_CONVERSION;

	ASSERT( nID >= 0 );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, level, name, pwd, email, phone, date_login, \
							   library_id, show_triage, show_library_view, show_my_view, show_process_view, show_current_view, \
							   show_completed_view, show_hold_view, show_highlights, show_duplicates, \
							   sort_ascending, print_full_request, bold_email_print, enable_thumbnail, \
							   completed_days, use_print_template, print_per_page, default_collection, default_ill_library \
							   FROM staff WHERE id=%I64d"), nID );

	// set -1 for not found
	//memset( pStaff, NULL, sizeof(STAFF_SET) );
	pStaff->m_id = -1;

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		pStaff->m_id = GetSQLLong(row[nRow++]);
		pStaff->m_level = GetSQLLong(row[nRow++]);
		pStaff->m_name = GetSQLString(row[nRow++]);
		pStaff->m_pwd = GetSQLString(row[nRow++]);
		pStaff->m_email = GetSQLString(row[nRow++]);
		pStaff->m_phone = GetSQLString(row[nRow++]);
		pStaff->m_date_login.ParseDateTime( GetSQLString(row[nRow++]) );
		pStaff->m_library_id = GetSQLLong(row[nRow++]);
		pStaff->m_show_triage = GetSQLLong(row[nRow++]);
		pStaff->m_show_library_view = GetSQLLong(row[nRow++]);
		pStaff->m_show_my_view = GetSQLLong(row[nRow++]);
		pStaff->m_show_process_view = GetSQLLong(row[nRow++]);
		pStaff->m_show_current_view = GetSQLLong(row[nRow++]);
		pStaff->m_show_completed_view = GetSQLLong(row[nRow++]);
		pStaff->m_show_hold_view = GetSQLLong(row[nRow++]);
		pStaff->m_show_highlights = GetSQLLong(row[nRow++]);
		pStaff->m_show_duplicates = GetSQLLong(row[nRow++]);
		pStaff->m_sort_ascending = GetSQLLong(row[nRow++]);
		pStaff->m_print_full_request = GetSQLLong(row[nRow++]);
		pStaff->m_bold_email_print = GetSQLLong(row[nRow++]);
		pStaff->m_enable_thumbnail = GetSQLLong(row[nRow++]);
		pStaff->m_completed_days = GetSQLLong(row[nRow++]);
		pStaff->m_use_print_template = GetSQLLong(row[nRow++]);
		pStaff->m_print_per_page = GetSQLLong(row[nRow++]);
		pStaff->m_default_collection = GetSQLLong(row[nRow++]);
		pStaff->m_default_ill_library = GetSQLLong(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetStaffSet(STAFF_SET *pStaff, CString szName)
{
	USES_CONVERSION;

	ASSERT( !szName.IsEmpty() );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, level, name, pwd, email, phone, date_login, \
							   library_id, show_triage, show_library_view, show_my_view, show_process_view, show_current_view, \
							   show_completed_view, show_hold_view, show_highlights, show_duplicates, \
							   sort_ascending, print_full_request, bold_email_print, enable_thumbnail, \
							   completed_days, use_print_template, print_per_page, default_collection, default_ill_library \
							   FROM staff WHERE name='%s'"), ES(szName) );

	// set -1 for not found
	//memset( pStaff, NULL, sizeof(STAFF_SET) );
	pStaff->m_id = -1;

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		pStaff->m_id = GetSQLLong(row[nRow++]);
		pStaff->m_level = GetSQLLong(row[nRow++]);
		pStaff->m_name = GetSQLString(row[nRow++]);
		pStaff->m_pwd = GetSQLString(row[nRow++]);
		pStaff->m_email = GetSQLString(row[nRow++]);
		pStaff->m_phone = GetSQLString(row[nRow++]);
		pStaff->m_date_login.ParseDateTime( GetSQLString(row[nRow++]) );
		pStaff->m_library_id = GetSQLLong(row[nRow++]);
		pStaff->m_show_triage = GetSQLLong(row[nRow++]);
		pStaff->m_show_library_view = GetSQLLong(row[nRow++]);
		pStaff->m_show_my_view = GetSQLLong(row[nRow++]);
		pStaff->m_show_process_view = GetSQLLong(row[nRow++]);
		pStaff->m_show_current_view = GetSQLLong(row[nRow++]);
		pStaff->m_show_completed_view = GetSQLLong(row[nRow++]);
		pStaff->m_show_hold_view = GetSQLLong(row[nRow++]);
		pStaff->m_show_highlights = GetSQLLong(row[nRow++]);
		pStaff->m_show_duplicates = GetSQLLong(row[nRow++]);
		pStaff->m_sort_ascending = GetSQLLong(row[nRow++]);
		pStaff->m_print_full_request = GetSQLLong(row[nRow++]);
		pStaff->m_bold_email_print = GetSQLLong(row[nRow++]);
		pStaff->m_enable_thumbnail = GetSQLLong(row[nRow++]);
		pStaff->m_completed_days = GetSQLLong(row[nRow++]);
		pStaff->m_use_print_template = GetSQLLong(row[nRow++]);
		pStaff->m_print_per_page = GetSQLLong(row[nRow++]);
		pStaff->m_default_collection = GetSQLLong(row[nRow++]);
		pStaff->m_default_ill_library = GetSQLLong(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetStaffSet(CArray<STAFF_SET> *pStaff, CString szFilter, CString szSort)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, level, name, pwd, email, phone, date_login, \
							   library_id, show_triage, show_library_view, show_my_view, show_process_view, show_current_view, \
							   show_completed_view, show_hold_view, show_highlights, show_duplicates, \
							   sort_ascending, print_full_request, bold_email_print, enable_thumbnail, \
							   completed_days, use_print_template, print_per_page, default_collection, default_ill_library \
							   FROM staff") );

	// add filter
	if( !szFilter.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE %s"), szQuery, szFilter );
		szQuery = szTemp;
	}

	// add sort
	if( !szSort.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s ORDER BY %s"), szQuery, szSort );
		szQuery = szTemp;
	}

	STAFF_SET Staff;
	// clear array
	pStaff->RemoveAll();

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		Staff.m_id = GetSQLLong(row[nRow++]);
		Staff.m_level = GetSQLLong(row[nRow++]);
		Staff.m_name = GetSQLString(row[nRow++]);
		Staff.m_pwd = GetSQLString(row[nRow++]);
		Staff.m_email = GetSQLString(row[nRow++]);
		Staff.m_phone = GetSQLString(row[nRow++]);
		Staff.m_date_login.ParseDateTime( GetSQLString(row[nRow++]) );
		Staff.m_library_id = GetSQLLong(row[nRow++]);
		Staff.m_show_triage = GetSQLLong(row[nRow++]);
		Staff.m_show_library_view = GetSQLLong(row[nRow++]);
		Staff.m_show_my_view = GetSQLLong(row[nRow++]);
		Staff.m_show_process_view = GetSQLLong(row[nRow++]);
		Staff.m_show_current_view = GetSQLLong(row[nRow++]);
		Staff.m_show_completed_view = GetSQLLong(row[nRow++]);
		Staff.m_show_hold_view = GetSQLLong(row[nRow++]);
		Staff.m_show_highlights = GetSQLLong(row[nRow++]);
		Staff.m_show_duplicates = GetSQLLong(row[nRow++]);
		Staff.m_sort_ascending = GetSQLLong(row[nRow++]);
		Staff.m_print_full_request = GetSQLLong(row[nRow++]);
		Staff.m_bold_email_print = GetSQLLong(row[nRow++]);
		Staff.m_enable_thumbnail = GetSQLLong(row[nRow++]);
		Staff.m_completed_days = GetSQLLong(row[nRow++]);
		Staff.m_use_print_template = GetSQLLong(row[nRow++]);
		Staff.m_print_per_page = GetSQLLong(row[nRow++]);
		Staff.m_default_collection = GetSQLLong(row[nRow++]);
		Staff.m_default_ill_library = GetSQLLong(row[nRow++]);
		// add staff
		pStaff->Add( Staff );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::SetStaffSet(STAFF_SET *pStaff, LONGLONG nID, BOOL bInsert)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	CString szDate;
	MYSQL_RES *res;
	MYSQL_ROW row;

	// check for existing
	if( bInsert )
	{
		szQuery.Format( _T("SELECT id FROM staff WHERE id=%I64d"), nID );
		res = QueryForRow( szQuery, &row );
		ULONG nCount = (ULONG)mysql_num_rows( res );
		mysql_free_result( res );

		if( nCount <= 0 )
		{
			szQuery.Format( _T("INSERT INTO staff \
							   (level, name, pwd, email, phone, \
							   library_id, show_triage, show_library_view, show_my_view, show_process_view, show_current_view, \
							   show_completed_view, show_hold_view, show_highlights, show_duplicates, \
							   sort_ascending, print_full_request, bold_email_print, enable_thumbnail, \
							   completed_days, use_print_template, print_per_page, default_collection, default_ill_library) \
							   VALUES \
							   (%ld, '%s', '%s', '%s', '%s', \
							   %ld, %ld, %ld, %ld, %ld, %ld, \
							   %ld, %ld, %ld, %ld, \
							   %ld, %ld, %ld, %ld, \
							   %ld, %ld, %ld, %d, %d)"),
							   pStaff->m_level, ES(pStaff->m_name), ES(pStaff->m_pwd), ES(pStaff->m_email), ES(pStaff->m_phone), 
							   pStaff->m_library_id, pStaff->m_show_triage, pStaff->m_show_library_view, pStaff->m_show_my_view, pStaff->m_show_process_view, pStaff->m_show_current_view, 
							   pStaff->m_show_completed_view, pStaff->m_show_hold_view, pStaff->m_show_highlights, pStaff->m_show_duplicates, 
							   pStaff->m_sort_ascending, pStaff->m_print_full_request, pStaff->m_bold_email_print, pStaff->m_enable_thumbnail, 
							   pStaff->m_completed_days, pStaff->m_use_print_template, pStaff->m_print_per_page, pStaff->m_default_collection, pStaff->m_default_ill_library );
		}
		else
		{
			bInsert = FALSE;
		}
	}

	// must be a UPDATE
	if( !bInsert )
	{
		szQuery.Format( _T("UPDATE staff SET \
							   level=%ld, name='%s', pwd='%s', email='%s', phone='%s', library_id=%ld, show_triage=%ld, \
							   show_library_view=%ld, show_my_view=%ld, show_process_view=%ld, show_current_view=%ld, show_completed_view=%ld, \
							   show_hold_view=%ld, show_highlights=%ld, show_duplicates=%ld, sort_ascending=%ld, \
							   print_full_request=%ld, bold_email_print=%ld, enable_thumbnail=%ld, completed_days=%ld, \
							   use_print_template=%ld, print_per_page=%ld, default_collection=%d, default_ill_library=%d "),
							   pStaff->m_level, ES(pStaff->m_name), ES(pStaff->m_pwd), ES(pStaff->m_email), ES(pStaff->m_phone), pStaff->m_library_id, pStaff->m_show_triage,
							   pStaff->m_show_library_view, pStaff->m_show_my_view, pStaff->m_show_process_view, pStaff->m_show_current_view, pStaff->m_show_completed_view,
							   pStaff->m_show_hold_view, pStaff->m_show_highlights, pStaff->m_show_duplicates, pStaff->m_sort_ascending,
							   pStaff->m_print_full_request, pStaff->m_bold_email_print, pStaff->m_enable_thumbnail, pStaff->m_completed_days,
							   pStaff->m_use_print_template, pStaff->m_print_per_page, pStaff->m_default_collection, pStaff->m_default_ill_library );

		// set ID
		CString szWhere;
		if( nID >= 0 )
			szWhere.Format( _T(" WHERE id=%ld"), nID );
		else
			szWhere.Format( _T(" WHERE id=%ld"), pStaff->m_id );

		// add where
		szQuery += szWhere;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	// get inserted ID
	if( bInsert )
	{
		nID = mysql_insert_id( &m_mysql );
		GetStaffSet( pStaff, nID );
	}

	Close();
	return TRUE;
}

void CMySQLEx::InitStaffSet(STAFF_SET *pStaff)
{
	pStaff->m_date_login.ParseDateTime( _T("") );
	pStaff->m_name.Empty();
	pStaff->m_email.Empty();
	pStaff->m_phone.Empty();
	pStaff->m_level = 0;
	pStaff->m_library_id = 0;
	pStaff->m_show_triage = FALSE;
	pStaff->m_show_library_view = TRUE;
	pStaff->m_show_my_view = TRUE;
	pStaff->m_show_process_view = TRUE;
	pStaff->m_show_current_view = FALSE;
	pStaff->m_show_completed_view = TRUE;
	pStaff->m_show_hold_view = TRUE;
	pStaff->m_show_highlights = TRUE;
	pStaff->m_show_duplicates = TRUE;
	pStaff->m_sort_ascending = TRUE;
	pStaff->m_print_full_request = TRUE;
	pStaff->m_bold_email_print = TRUE;
	pStaff->m_enable_thumbnail = TRUE;
	pStaff->m_completed_days = 0;
	pStaff->m_use_print_template = FALSE;
	pStaff->m_print_per_page = 1;
	pStaff->m_default_collection = 0;
	pStaff->m_default_ill_library = 0;
}

void CMySQLEx::InitUserSet(USER_SET *pUser)
{
	pUser->m_id = -1;
	pUser->m_level = 0;
	pUser->m_library_id = 0;
	pUser->m_taxable = 0;
	pUser->m_fee_max = 0;
	pUser->m_activation_code = 0;
	pUser->m_activated = 0;
	pUser->m_notify = 0;
	pUser->m_type = 0;
	pUser->m_method_id = 0;
	pUser->m_referrals = 0;
	pUser->m_copyright = 0;
}

void CMySQLEx::InitRequestSet(REQUEST_SET *pRequest)
{
	// request
	pRequest->m_id = -1;
	pRequest->m_staff_id = 0;
	pRequest->m_user_id = 0;
	pRequest->m_status_id = 0;
	pRequest->m_collection_id = 0;
	pRequest->m_library_id = 0;
	pRequest->m_atlibrary = 0;
	pRequest->m_step_id = 0;
	pRequest->m_priority_id = 0;
	pRequest->m_method_id = 0;
	pRequest->m_ill_id = 0;
	pRequest->m_viewed = 0;
	pRequest->m_redirected = 0;
	pRequest->m_touched = 0;
	pRequest->m_webcheck = 0;
	pRequest->m_deleted = 0;
	pRequest->m_invoiced = 0;
	pRequest->m_type = 0;
	pRequest->m_level = 0;
	pRequest->m_taxable = 0;
	pRequest->m_overdue = 0;
	pRequest->m_copyright = 0;
	pRequest->m_canceled = 0;
	pRequest->m_resubmitted = 0;
	pRequest->m_finalized = 0;
	pRequest->m_referrals = 0;
	pRequest->m_manual = 0;
	pRequest->m_material = 0;
	pRequest->m_fee_copyright = 0;
	pRequest->m_fee_service = 0;
	pRequest->m_fee_misc = 0;
	pRequest->m_external_id = 0;
	pRequest->m_retain = 0;
}

BOOL CMySQLEx::GetDescriptionUser(USER_LOOKUP *pUser, LONGLONG nID)
{
	USES_CONVERSION;

	ASSERT( nID >= 0 );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, name, email, company, address1, address2, phone, fax FROM users WHERE id=%I64d"), nID );

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		// Users
		pUser->nID = GetSQLLong(row[nRow++]);
		pUser->szName = GetSQLString(row[nRow++]);
		pUser->szEmail = GetSQLString(row[nRow++]);
		pUser->szCompany = GetSQLString(row[nRow++]);
		pUser->szAddress1 = GetSQLString(row[nRow++]);
		pUser->szAddress2 = GetSQLString(row[nRow++]);
		pUser->szPhone = GetSQLString(row[nRow++]);
		pUser->szFax = GetSQLString(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetDescriptionUsers(CArray<USER_LOOKUP> *pUser, LPCTSTR lpszSort, LPCTSTR lpszFilter, BOOL bSetAt)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, name, email, company, address1, address2, phone, fax FROM users") );

	// add filter
	if( lpszFilter != NULL )
	{
		if( _tcslen( lpszFilter ) > 0 )
		{
			CString szTemp;
			szTemp.Format( _T("%s WHERE %s"), szQuery, lpszFilter );
			szQuery = szTemp;
		}
	}

	// add sort
	if( lpszSort != NULL )
	{
		if( _tcslen( lpszSort ) > 0 )
		{
			CString szTemp;
			szTemp.Format( _T("%s ORDER BY %s"), szQuery, lpszSort );
			szQuery = szTemp;
		}
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	USER_LOOKUP User;
	// clear array
	pUser->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		// Users
		User.nID = GetSQLLong(row[nRow++]);
		User.szName = GetSQLString(row[nRow++]);
		User.szEmail = GetSQLString(row[nRow++]);
		User.szCompany = GetSQLString(row[nRow++]);
		User.szAddress1 = GetSQLString(row[nRow++]);
		User.szAddress2 = GetSQLString(row[nRow++]);
		User.szPhone = GetSQLString(row[nRow++]);
		User.szFax = GetSQLString(row[nRow++]);
		// insert
		if( bSetAt )
			pUser->SetAtGrow( User.nID, User );
		else
			pUser->Add( User );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetUserSet(CArray<USER_SET> *pUser)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, level, lan_id, name, pwd, email, company, phone, fax, shipto, address1, address2, city, state, zipcode, country, \
					   billto, billaddress1, billaddress2, billcity, billstate, billzipcode, billcountry, \
					   altemail1, altemail2, altemail3, altemail4, altemail5, \
					   library_id, date_join, date_login, taxable, fee_max, activation_code, activated, notify, type, method_id, referrals, copyright, \
					   account_number, shipping_number, external, notes, external_id \
					   FROM users") );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	USER_SET User;
	// clear array
	pUser->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		// Users
		User.m_id = GetSQLLong(row[nRow++]);
		User.m_level = GetSQLLong(row[nRow++]);
		User.m_lan_id = GetSQLString(row[nRow++]);
		User.m_name = GetSQLString(row[nRow++]);
		User.m_pwd = GetSQLString(row[nRow++]);
		User.m_email = GetSQLString(row[nRow++]);
		User.m_company = GetSQLString(row[nRow++]);
		User.m_phone = GetSQLString(row[nRow++]);
		User.m_fax = GetSQLString(row[nRow++]);
		User.m_shipto = GetSQLString(row[nRow++]);
		User.m_address1 = GetSQLString(row[nRow++]);
		User.m_address2 = GetSQLString(row[nRow++]);
		User.m_city = GetSQLString(row[nRow++]);
		User.m_state = GetSQLString(row[nRow++]);
		User.m_zipcode = GetSQLString(row[nRow++]);
		User.m_country = GetSQLString(row[nRow++]);
		User.m_billto = GetSQLString(row[nRow++]);
		User.m_billaddress1 = GetSQLString(row[nRow++]);
		User.m_billaddress2 = GetSQLString(row[nRow++]);
		User.m_billcity = GetSQLString(row[nRow++]);
		User.m_billstate = GetSQLString(row[nRow++]);
		User.m_billzipcode = GetSQLString(row[nRow++]);
		User.m_billcountry = GetSQLString(row[nRow++]);
		User.m_altemail1 = GetSQLString(row[nRow++]);
		User.m_altemail2 = GetSQLString(row[nRow++]);
		User.m_altemail3 = GetSQLString(row[nRow++]);
		User.m_altemail4 = GetSQLString(row[nRow++]);
		User.m_altemail5 = GetSQLString(row[nRow++]);
		User.m_library_id = GetSQLLong(row[nRow++]);
		User.m_date_join.ParseDateTime(GetSQLString(row[nRow++]));
		User.m_date_login.ParseDateTime(GetSQLString(row[nRow++]));
		User.m_taxable = GetSQLLong(row[nRow++]);
		User.m_fee_max = GetSQLLong(row[nRow++]);
		User.m_activation_code = GetSQLLong(row[nRow++]);
		User.m_activated = GetSQLLong(row[nRow++]);
		User.m_notify = GetSQLLong(row[nRow++]);
		User.m_type = GetSQLLong(row[nRow++]);
		User.m_method_id = GetSQLLong(row[nRow++]);
		User.m_referrals = GetSQLLong(row[nRow++]);
		User.m_copyright = GetSQLLong(row[nRow++]);
		User.m_account_number = GetSQLString(row[nRow++]);
		User.m_shipping_number = GetSQLString(row[nRow++]);
		User.m_external = GetSQLLong(row[nRow++]);
		User.m_notes = GetSQLString(row[nRow++]);
		User.m_external_id = GetSQLLong(row[nRow++]);
		// insert
		pUser->Add( User );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetUserSet(CArray<USER_SET> *pUser, CString szFilter, CString szSort )
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, level, lan_id, name, pwd, email, company, phone, fax, shipto, address1, address2, city, state, zipcode, country, \
					   billto, billaddress1, billaddress2, billcity, billstate, billzipcode, billcountry, \
					   altemail1, altemail2, altemail3, altemail4, altemail5, \
					   library_id, date_join, date_login, taxable, fee_max, activation_code, activated, notify, type, method_id, referrals, copyright, \
					   account_number, shipping_number, external, notes, external_id \
					   FROM users") );

	// add filter
	if( !szFilter.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE %s"), szQuery, szFilter );
		szQuery = szTemp;
	}

	// add sort
	if( !szSort.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s ORDER BY %s"), szQuery, szSort );
		szQuery = szTemp;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	USER_SET User;
	// clear array
	pUser->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		// Users
		User.m_id = GetSQLLong(row[nRow++]);
		User.m_level = GetSQLLong(row[nRow++]);
		User.m_lan_id = GetSQLString(row[nRow++]);
		User.m_name = GetSQLString(row[nRow++]);
		User.m_pwd = GetSQLString(row[nRow++]);
		User.m_email = GetSQLString(row[nRow++]);
		User.m_company = GetSQLString(row[nRow++]);
		User.m_phone = GetSQLString(row[nRow++]);
		User.m_fax = GetSQLString(row[nRow++]);
		User.m_shipto = GetSQLString(row[nRow++]);
		User.m_address1 = GetSQLString(row[nRow++]);
		User.m_address2 = GetSQLString(row[nRow++]);
		User.m_city = GetSQLString(row[nRow++]);
		User.m_state = GetSQLString(row[nRow++]);
		User.m_zipcode = GetSQLString(row[nRow++]);
		User.m_country = GetSQLString(row[nRow++]);
		User.m_billto = GetSQLString(row[nRow++]);
		User.m_billaddress1 = GetSQLString(row[nRow++]);
		User.m_billaddress2 = GetSQLString(row[nRow++]);
		User.m_billcity = GetSQLString(row[nRow++]);
		User.m_billstate = GetSQLString(row[nRow++]);
		User.m_billzipcode = GetSQLString(row[nRow++]);
		User.m_billcountry = GetSQLString(row[nRow++]);
		User.m_altemail1 = GetSQLString(row[nRow++]);
		User.m_altemail2 = GetSQLString(row[nRow++]);
		User.m_altemail3 = GetSQLString(row[nRow++]);
		User.m_altemail4 = GetSQLString(row[nRow++]);
		User.m_altemail5 = GetSQLString(row[nRow++]);
		User.m_library_id = GetSQLLong(row[nRow++]);
		User.m_date_join.ParseDateTime(GetSQLString(row[nRow++]));
		User.m_date_login.ParseDateTime(GetSQLString(row[nRow++]));
		User.m_taxable = GetSQLLong(row[nRow++]);
		User.m_fee_max = GetSQLLong(row[nRow++]);
		User.m_activation_code = GetSQLLong(row[nRow++]);
		User.m_activated = GetSQLLong(row[nRow++]);
		User.m_notify = GetSQLLong(row[nRow++]);
		User.m_type = GetSQLLong(row[nRow++]);
		User.m_method_id = GetSQLLong(row[nRow++]);
		User.m_referrals = GetSQLLong(row[nRow++]);
		User.m_copyright = GetSQLLong(row[nRow++]);
		User.m_account_number = GetSQLString(row[nRow++]);
		User.m_shipping_number = GetSQLString(row[nRow++]);
		User.m_external = GetSQLLong(row[nRow++]);
		User.m_notes = GetSQLString(row[nRow++]);
		User.m_external_id = GetSQLLong(row[nRow++]);
		// insert
		pUser->Add( User );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetUserSet(USER_SET *pUser, LONGLONG nID)
{
	USES_CONVERSION;

	ASSERT( nID >= 0 );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, level, lan_id, name, pwd, email, company, phone, fax, shipto, address1, address2, city, state, zipcode, country, \
					   billto, billaddress1, billaddress2, billcity, billstate, billzipcode, billcountry, \
					   altemail1, altemail2, altemail3, altemail4, altemail5, \
					   library_id, date_join, date_login, taxable, fee_max, activation_code, activated, notify, type, method_id, referrals, copyright, \
					   account_number, shipping_number, external, notes, external_id \
					   FROM users") );

	// add filter
	if( nID > 0 )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE id=%I64d"), szQuery, nID );
		szQuery = szTemp;
	}

	// set -1 for not found
	//memset( pStaff, NULL, sizeof(STAFF_SET) );
	pUser->m_id = -1;

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		// Users
		pUser->m_id = GetSQLLong(row[nRow++]);
		pUser->m_level = GetSQLLong(row[nRow++]);
		pUser->m_lan_id = GetSQLString(row[nRow++]);
		pUser->m_name = GetSQLString(row[nRow++]);
		pUser->m_pwd = GetSQLString(row[nRow++]);
		pUser->m_email = GetSQLString(row[nRow++]);
		pUser->m_company = GetSQLString(row[nRow++]);
		pUser->m_phone = GetSQLString(row[nRow++]);
		pUser->m_fax = GetSQLString(row[nRow++]);
		pUser->m_shipto = GetSQLString(row[nRow++]);
		pUser->m_address1 = GetSQLString(row[nRow++]);
		pUser->m_address2 = GetSQLString(row[nRow++]);
		pUser->m_city = GetSQLString(row[nRow++]);
		pUser->m_state = GetSQLString(row[nRow++]);
		pUser->m_zipcode = GetSQLString(row[nRow++]);
		pUser->m_country = GetSQLString(row[nRow++]);
		pUser->m_billto = GetSQLString(row[nRow++]);
		pUser->m_billaddress1 = GetSQLString(row[nRow++]);
		pUser->m_billaddress2 = GetSQLString(row[nRow++]);
		pUser->m_billcity = GetSQLString(row[nRow++]);
		pUser->m_billstate = GetSQLString(row[nRow++]);
		pUser->m_billzipcode = GetSQLString(row[nRow++]);
		pUser->m_billcountry = GetSQLString(row[nRow++]);
		pUser->m_altemail1 = GetSQLString(row[nRow++]);
		pUser->m_altemail2 = GetSQLString(row[nRow++]);
		pUser->m_altemail3 = GetSQLString(row[nRow++]);
		pUser->m_altemail4 = GetSQLString(row[nRow++]);
		pUser->m_altemail5 = GetSQLString(row[nRow++]);
		pUser->m_library_id = GetSQLLong(row[nRow++]);
		pUser->m_date_join.ParseDateTime(GetSQLString(row[nRow++]));
		pUser->m_date_login.ParseDateTime(GetSQLString(row[nRow++]));
		pUser->m_taxable = GetSQLLong(row[nRow++]);
		pUser->m_fee_max = GetSQLLong(row[nRow++]);
		pUser->m_activation_code = GetSQLLong(row[nRow++]);
		pUser->m_activated = GetSQLLong(row[nRow++]);
		pUser->m_notify = GetSQLLong(row[nRow++]);
		pUser->m_type = GetSQLLong(row[nRow++]);
		pUser->m_method_id = GetSQLLong(row[nRow++]);
		pUser->m_referrals = GetSQLLong(row[nRow++]);
		pUser->m_copyright = GetSQLLong(row[nRow++]);
		pUser->m_account_number = GetSQLString(row[nRow++]);
		pUser->m_shipping_number = GetSQLString(row[nRow++]);
		pUser->m_external = GetSQLLong(row[nRow++]);
		pUser->m_notes = GetSQLString(row[nRow++]);
		pUser->m_external_id = GetSQLLong(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetUserSet(USER_SET *pUser, CString szEmail)
{
	USES_CONVERSION;

	ASSERT( !szEmail.IsEmpty() );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, level, lan_id, name, pwd, email, company, phone, fax, shipto, address1, address2, city, state, zipcode, country, \
					   billto, billaddress1, billaddress2, billcity, billstate, billzipcode, billcountry, \
					   altemail1, altemail2, altemail3, altemail4, altemail5, \
					   library_id, date_join, date_login, taxable, fee_max, activation_code, activated, notify, type, method_id, referrals, copyright, \
					   account_number, shipping_number, external, notes, external_id \
					   FROM users") );

	// add filter
	if( !szEmail.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE email='%s'"), szQuery, ES(szEmail) );
		szQuery = szTemp;
	}

	// set -1 for not found
	//memset( pUser, NULL, sizeof(STAFF_SET) );
	pUser->m_id = -1;

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		// Users
		pUser->m_id = GetSQLLong(row[nRow++]);
		pUser->m_level = GetSQLLong(row[nRow++]);
		pUser->m_lan_id = GetSQLString(row[nRow++]);
		pUser->m_name = GetSQLString(row[nRow++]);
		pUser->m_pwd = GetSQLString(row[nRow++]);
		pUser->m_email = GetSQLString(row[nRow++]);
		pUser->m_company = GetSQLString(row[nRow++]);
		pUser->m_phone = GetSQLString(row[nRow++]);
		pUser->m_fax = GetSQLString(row[nRow++]);
		pUser->m_shipto = GetSQLString(row[nRow++]);
		pUser->m_address1 = GetSQLString(row[nRow++]);
		pUser->m_address2 = GetSQLString(row[nRow++]);
		pUser->m_city = GetSQLString(row[nRow++]);
		pUser->m_state = GetSQLString(row[nRow++]);
		pUser->m_zipcode = GetSQLString(row[nRow++]);
		pUser->m_country = GetSQLString(row[nRow++]);
		pUser->m_billto = GetSQLString(row[nRow++]);
		pUser->m_billaddress1 = GetSQLString(row[nRow++]);
		pUser->m_billaddress2 = GetSQLString(row[nRow++]);
		pUser->m_billcity = GetSQLString(row[nRow++]);
		pUser->m_billstate = GetSQLString(row[nRow++]);
		pUser->m_billzipcode = GetSQLString(row[nRow++]);
		pUser->m_billcountry = GetSQLString(row[nRow++]);
		pUser->m_altemail1 = GetSQLString(row[nRow++]);
		pUser->m_altemail2 = GetSQLString(row[nRow++]);
		pUser->m_altemail3 = GetSQLString(row[nRow++]);
		pUser->m_altemail4 = GetSQLString(row[nRow++]);
		pUser->m_altemail5 = GetSQLString(row[nRow++]);
		pUser->m_library_id = GetSQLLong(row[nRow++]);
		pUser->m_date_join.ParseDateTime(GetSQLString(row[nRow++]));
		pUser->m_date_login.ParseDateTime(GetSQLString(row[nRow++]));
		pUser->m_taxable = GetSQLLong(row[nRow++]);
		pUser->m_fee_max = GetSQLLong(row[nRow++]);
		pUser->m_activation_code = GetSQLLong(row[nRow++]);
		pUser->m_activated = GetSQLLong(row[nRow++]);
		pUser->m_notify = GetSQLLong(row[nRow++]);
		pUser->m_type = GetSQLLong(row[nRow++]);
		pUser->m_method_id = GetSQLLong(row[nRow++]);
		pUser->m_referrals = GetSQLLong(row[nRow++]);
		pUser->m_copyright = GetSQLLong(row[nRow++]);
		pUser->m_account_number = GetSQLString(row[nRow++]);
		pUser->m_shipping_number = GetSQLString(row[nRow++]);
		pUser->m_external = GetSQLLong(row[nRow++]);
		pUser->m_notes = GetSQLString(row[nRow++]);
		pUser->m_external_id = GetSQLLong(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::SetUserSet(USER_SET *pUser, LONGLONG nID, BOOL bInsert)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	CString szDate;
	MYSQL_RES *res;
	MYSQL_ROW row;

	// default to 1 for now
	pUser->m_level = 1;

	// check for existing
	if( bInsert )
	{
		szQuery.Format( _T("SELECT id FROM users WHERE id=%I64d"), nID );
		res = QueryForRow( szQuery, &row );
		ULONG nCount = (ULONG)mysql_num_rows( res );
		mysql_free_result( res );

		if( nCount <= 0 )
		{
			szQuery.Format( _T("INSERT INTO users \
							   (level, lan_id, name, pwd, email, company, phone, fax,\
								shipto, address1, address2, city, state, zipcode, country,\
								billto, billaddress1, billaddress2, billcity, billstate, billzipcode, billcountry,\
								altemail1, altemail2, altemail3, altemail4, altemail5,\
								library_id, taxable, fee_max,\
								activation_code, activated, notify, type, method_id, referrals, copyright,\
								account_number, shipping_number, external, notes, external_id)\
							   VALUES\
							   (%d, '%s', '%s', '%s', '%s', '%s', '%s', '%s',\
							   '%s', '%s', '%s', '%s', '%s', '%s', '%s',\
							   '%s', '%s', '%s', '%s', '%s', '%s', '%s',\
							   '%s', '%s', '%s', '%s', '%s',\
							   %d, %d, %f,\
							   %d, %d, %d, %d, %d, %d, %d,\
							   '%s', '%s', %d, '%s', %ld)"),
							   pUser->m_level, ES(pUser->m_lan_id), ES(pUser->m_name), ES(pUser->m_pwd), ES(pUser->m_email), ES(pUser->m_company), ES(pUser->m_phone), ES(pUser->m_fax), 
							   ES(pUser->m_shipto), ES(pUser->m_address1), ES(pUser->m_address2), ES(pUser->m_city), ES(pUser->m_state), ES(pUser->m_zipcode), ES(pUser->m_country), 
							   ES(pUser->m_billto), ES(pUser->m_billaddress1), ES(pUser->m_billaddress2), ES(pUser->m_billcity), ES(pUser->m_billstate), ES(pUser->m_billzipcode), ES(pUser->m_billcountry), 
							   ES(pUser->m_altemail1), ES(pUser->m_altemail2), ES(pUser->m_altemail3), ES(pUser->m_altemail4), ES(pUser->m_altemail5), 
							   pUser->m_library_id, pUser->m_taxable, pUser->m_fee_max, 
							   pUser->m_activation_code, pUser->m_activated, pUser->m_notify, pUser->m_type, pUser->m_method_id, pUser->m_referrals, pUser->m_copyright,
							   ES(pUser->m_account_number), ES(pUser->m_shipping_number), pUser->m_external, ES(pUser->m_notes), pUser->m_external_id );
		}
		else
		{
			bInsert = FALSE;
		}
	}

	// must be a UPDATE
	if( !bInsert )
	{
		szQuery.Format( _T("UPDATE users SET \
						   level=%d, lan_id='%s', name='%s', pwd='%s', email='%s', company='%s', phone='%s', fax='%s',\
						   shipto='%s', address1='%s', address2='%s', city='%s', state='%s', zipcode='%s', country='%s',\
						   billto='%s', billaddress1='%s', billaddress2='%s', billcity='%s', billstate='%s', billzipcode='%s', billcountry='%s',\
						   altemail1='%s', altemail2='%s', altemail3='%s', altemail4='%s', altemail5='%s',\
						   library_id=%d, taxable=%d, fee_max=%f,\
						   activation_code=%d, activated=%d, notify=%d, type=%d, method_id=%d, referrals=%d, copyright=%d,\
						   account_number='%s', shipping_number='%s', external=%d, notes='%s', external_id=%ld "),
						   pUser->m_level, ES(pUser->m_lan_id), ES(pUser->m_name), ES(pUser->m_pwd), ES(pUser->m_email), ES(pUser->m_company), ES(pUser->m_phone), ES(pUser->m_fax), 
						   ES(pUser->m_shipto), ES(pUser->m_address1), ES(pUser->m_address2), ES(pUser->m_city), ES(pUser->m_state), ES(pUser->m_zipcode), ES(pUser->m_country), 
						   ES(pUser->m_billto), ES(pUser->m_billaddress1), ES(pUser->m_billaddress2), ES(pUser->m_billcity), ES(pUser->m_billstate), ES(pUser->m_billzipcode), ES(pUser->m_billcountry), 
						   ES(pUser->m_altemail1), ES(pUser->m_altemail2), ES(pUser->m_altemail3), ES(pUser->m_altemail4), ES(pUser->m_altemail5), 
						   pUser->m_library_id, pUser->m_taxable, pUser->m_fee_max, 
						   pUser->m_activation_code, pUser->m_activated, pUser->m_notify, pUser->m_type, pUser->m_method_id, pUser->m_referrals, pUser->m_copyright,
						   ES(pUser->m_account_number), ES(pUser->m_shipping_number), pUser->m_external, ES(pUser->m_notes), pUser->m_external_id );

		// set ID
		CString szWhere;
		if( nID >= 0 )
			szWhere.Format( _T(" WHERE id=%I64d"), nID );
		else
			szWhere.Format( _T(" WHERE id=%ld"), pUser->m_id );

		// add where
		szQuery += szWhere;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	// close local
	Close();

	// update external request
	if( pUser->m_external_id > 0 )
		SetExternalUserSet( pUser, pUser->m_external_id );

	return TRUE;
}

BOOL CMySQLEx::SetUserSet(LDAP_USER_INFO *pUser, BOOL bInsert, LONGLONG *pnID)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	CString szDate;
	MYSQL_RES *res;
	MYSQL_ROW row;

	szQuery.Format( _T("SELECT id FROM users WHERE email='%s'"), ES(pUser->szEmail) );
	res = QueryForRow( szQuery, &row );
	ULONG nCount = (ULONG)mysql_num_rows( res );
	mysql_free_result( res );

	// check if already there
	if( nCount > 0 && bInsert )
	{
		AfxMessageBox( _T("User already in database.") );
		return FALSE;
	}

	// insert or update
	if( bInsert )
	{
		COleDateTime time = COleDateTime::GetCurrentTime();
		INT nCode = rand();
		CString szHash;
		szHash.Format( _T("%d"), nCode );
		appConfig.Hash( &szHash );

		szQuery.Format( _T("INSERT INTO users SET date_join='%s', activated=1, \
						   lan_id='%s', email='%s', \
						   name='%s', \
						   phone='%s', address1='%s', shipto='%s', pwd='%s', activation_code=%d"),
						   time.Format( _T("%Y-%m-%d %H:%M:%S") ), 
						   ES(pUser->szLANID), ES(pUser->szEmail), 
						   ES(pUser->szName), 
						   ES(pUser->szPhone), ES(pUser->szAddress), ES(pUser->szName), ES(szHash), nCode );
	}
	else
	{
		szQuery.Format( _T("UPDATE users SET lan_id='%s', name='%s', phone='%s', address1='%s', shipto='%s' WHERE email='%s'"),
			ES(pUser->szLANID), ES(pUser->szName), ES(pUser->szPhone), ES(pUser->szAddress), ES(pUser->szName), ES(pUser->szEmail) );
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));

		// append SQL
		if( appConfig.m_General.bTestSystem )
			szError += _T("\n\n") + szQuery;

		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	// get inserted ID
	if( bInsert && pnID != NULL )
	{
		LONGLONG nInsertID = mysql_insert_id( &m_mysql );
		*pnID = nInsertID;
	}
	else
	{
		*pnID = -1;
	}

	Close();
	return TRUE;
}

BOOL CMySQLEx::SetExternalUserSet(USER_SET *pUser, LONGLONG nID)
{
	USES_CONVERSION;

	// connect
	if( !Connect( TRUE ) )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	CString szDate;

	// default to 1 for now
	pUser->m_level = 1;

	// build query
	szQuery.Format( _T("UPDATE users SET \
					   level=%d, name='%s', pwd='%s', email='%s', company='%s', phone='%s', fax='%s',\
					   shipto='%s', address1='%s', address2='%s', city='%s', state='%s', zipcode='%s', country='%s',\
					   billto='%s', billaddress1='%s', billaddress2='%s', billcity='%s', billstate='%s', billzipcode='%s', billcountry='%s',\
					   altemail1='%s', altemail2='%s', altemail3='%s', altemail4='%s', altemail5='%s',\
					   library_id=%d, taxable=%d, fee_max=%f,\
					   activation_code=%d, activated=%d, notify=%d, type=%d, method_id=%d, referrals=%d, copyright=%d,\
					   account_number='%s', shipping_number='%s', external=%d, notes='%s' "),
					   pUser->m_level, ES(pUser->m_name), ES(pUser->m_pwd), ES(pUser->m_email), ES(pUser->m_company), ES(pUser->m_phone), ES(pUser->m_fax), 
					   ES(pUser->m_shipto), ES(pUser->m_address1), ES(pUser->m_address2), ES(pUser->m_city), ES(pUser->m_state), ES(pUser->m_zipcode), ES(pUser->m_country), 
					   ES(pUser->m_billto), ES(pUser->m_billaddress1), ES(pUser->m_billaddress2), ES(pUser->m_billcity), ES(pUser->m_billstate), ES(pUser->m_billzipcode), ES(pUser->m_billcountry), 
					   ES(pUser->m_altemail1), ES(pUser->m_altemail2), ES(pUser->m_altemail3), ES(pUser->m_altemail4), ES(pUser->m_altemail5), 
					   pUser->m_library_id, pUser->m_taxable, pUser->m_fee_max, 
					   pUser->m_activation_code, pUser->m_activated, pUser->m_notify, pUser->m_type, pUser->m_method_id, pUser->m_referrals, pUser->m_copyright,
					   ES(pUser->m_account_number), ES(pUser->m_shipping_number), pUser->m_external, ES(pUser->m_notes) );

	// set ID
	CString szWhere;
	if( nID >= 0 )
		szWhere.Format( _T(" WHERE id=%I64d"), nID );
	else
		szWhere.Format( _T(" WHERE id=%ld"), pUser->m_id );

	// add where
	szQuery += szWhere;

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetExternalUserID(USER_SET *pUser, CString szEmail)
{
	USES_CONVERSION;

	// connect
	if( !Connect( TRUE ) )
	{
		ASSERT( FALSE );
		return FALSE;
	}
	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, pwd FROM users WHERE email='%s'"), ES(szEmail) );

	pUser->m_id = -1;

	res = QueryForRow( szQuery, &row );
	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		// Users
		pUser->m_id = GetSQLLong(row[nRow++]);
		pUser->m_pwd = GetSQLString(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetExternIllSet(CArray<EXTERNAL_ILL_SET> *pIll, CString szFilter, CString szSort)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, email, company, sent, date_join, date_out FROM external_ill") );

	// add filter
	if( !szFilter.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE %s"), szQuery, szFilter );
		szQuery = szTemp;
	}

	// add sort
	if( !szSort.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s ORDER BY %s"), szQuery, szSort );
		szQuery = szTemp;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	EXTERNAL_ILL_SET Ill;
	// clear array
	pIll->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		Ill.m_id = GetSQLLong(row[nRow++]);
		Ill.m_email = GetSQLString(row[nRow++]);
		Ill.m_company = GetSQLString(row[nRow++]);
		Ill.m_sent = GetSQLLong(row[nRow++]);
		Ill.m_date_join.ParseDateTime(GetSQLString(row[nRow++]));
		Ill.m_date_out.ParseDateTime(GetSQLString(row[nRow++]));
		// insert
		pIll->Add( Ill );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetExternIllSet(EXTERNAL_ILL_SET *pIll, CString szEmail)
{
	USES_CONVERSION;

	ASSERT( !szEmail.IsEmpty() );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, email, company, sent, date_join, date_out FROM external_ill WHERE email='%s'"), ES(szEmail) );

	// set -1 for not found
	pIll->m_id = -1;

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		pIll->m_id = GetSQLLong(row[nRow++]);
		pIll->m_email = GetSQLString(row[nRow++]);
		pIll->m_company = GetSQLString(row[nRow++]);
		pIll->m_sent = GetSQLLong(row[nRow++]);
		pIll->m_date_join.ParseDateTime(GetSQLString(row[nRow++]));
		pIll->m_date_out.ParseDateTime(GetSQLString(row[nRow++]));
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::SetExternIllSet(EXTERNAL_ILL_SET *pIll, CString szEmail, BOOL bInsert)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	MYSQL_RES *res;
	MYSQL_ROW row;

	// check for existing
	if( bInsert )
	{
		szQuery.Format( _T("SELECT id FROM external_ill WHERE email='%s'"), ES(szEmail) );
		res = QueryForRow( szQuery, &row );
		ULONG nCount = (ULONG)mysql_num_rows( res );
		mysql_free_result( res );

		if( nCount <= 0 )
		{
			COleDateTime time = COleDateTime::GetCurrentTime();

			pIll->m_sent = 0;
			pIll->m_date_join = time;
			pIll->m_date_out = time;

			szQuery.Format( _T("INSERT INTO external_ill (email, company, sent, date_join, date_out) VALUES ('%s', '%s', %ld, '%s', '%s')"),
				ES(pIll->m_email), ES(pIll->m_company), pIll->m_sent, pIll->m_date_join.Format( _T("%Y-%m-%d %H:%M:%S") ), pIll->m_date_out.Format( _T("%Y-%m-%d %H:%M:%S") ) );
		}
		else
		{
			bInsert = FALSE;
		}
	}

	// must be a UPDATE
	if( !bInsert )
	{
		COleDateTime time = COleDateTime::GetCurrentTime();

		pIll->m_date_out = time;

		szQuery.Format( _T("UPDATE external_ill SET email='%s', company='%s', sent=%ld, date_join='%s', date_out='%s'"),
			ES(pIll->m_email), ES(pIll->m_company), pIll->m_sent, pIll->m_date_join.Format( _T("%Y-%m-%d %H:%M:%S") ), pIll->m_date_out.Format( _T("%Y-%m-%d %H:%M:%S") ) );

		// set ID
		CString szWhere;
		if( !szEmail.IsEmpty() )
			szWhere.Format( _T(" WHERE email='%s'"), ES(szEmail) );
		else
			szWhere.Format( _T(" WHERE email='%s'"), ES(pIll->m_email) );

		// add where
		szQuery += szWhere;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	Close();
	return TRUE;

}

BOOL CMySQLEx::SetExternIllSet(EXTERNAL_ILL_SET *pIll, LONGLONG nID)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;

	// UPDATE
	szQuery.Format( _T("UPDATE external_ill SET email='%s', company='%s', sent=%ld, date_join='%s', date_out='%s'"),
		ES(pIll->m_email), ES(pIll->m_company), pIll->m_sent, pIll->m_date_join.Format( _T("%Y-%m-%d %H:%M:%S") ), pIll->m_date_out.Format( _T("%Y-%m-%d %H:%M:%S") ) );

	// set ID
	CString szWhere;
	szWhere.Format( _T(" WHERE id=%I64d"), nID );

	// add where
	szQuery += szWhere;

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	Close();
	return TRUE;

}

BOOL CMySQLEx::GetExternIllHistory(EXTERNAL_ILL_HISTORY *pIll, LONGLONG nID)
{
	USES_CONVERSION;

	ASSERT( nID >= 0 );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT staff_id, user_id, date_out, description, link FROM external_ill_history WHERE user_id=%I64d"), nID );

	res = QueryForRow( szQuery, &row );
	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		pIll->m_staff_id = GetSQLLong(row[nRow++]);
		pIll->m_user_id = GetSQLLong(row[nRow++]);
		pIll->m_date_out.ParseDateTime( GetSQLString(row[nRow++]) );
		pIll->m_description = GetSQLString(row[nRow++]);
		pIll->m_link = GetSQLString(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetExternIllHistorys(CArray<EXTERNAL_ILL_HISTORY> *pIll, CString szFilter, CString szSort)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT external_ill_history.staff_id, external_ill_history.user_id, external_ill_history.date_out, \
					   external_ill_history.description, external_ill_history.link, \
					   staff.name, \
					   external_ill.email \
					   FROM external_ill_history \
					   JOIN staff ON staff.id=external_ill_history.staff_id \
					   JOIN external_ill ON external_ill.id=external_ill_history.user_id") );

	// add filter
	if( !szFilter.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE %s"), szQuery, szFilter );
		szQuery = szTemp;
	}

	// add sort
	if( !szSort.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s ORDER BY %s"), szQuery, szSort );
		szQuery = szTemp;
	}

	// add limit
	szQuery += _T(" LIMIT 65536");	

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	EXTERNAL_ILL_HISTORY Ill;
	// clear array
	pIll->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		Ill.m_staff_id = GetSQLLong(row[nRow++]);
		Ill.m_user_id = GetSQLLong(row[nRow++]);
		Ill.m_date_out.ParseDateTime( GetSQLString(row[nRow++]) );
		Ill.m_description = GetSQLString(row[nRow++]);
		Ill.m_link = GetSQLString(row[nRow++]);
		Ill.m_staff_name = GetSQLString(row[nRow++]);
		Ill.m_user_email = GetSQLString(row[nRow++]);
		// insert
		pIll->Add( Ill );
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::UpdateExternIllHistory(LONGLONG nStaffID, LONGLONG nUserID, CString szDescription, CString szLink)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szDate;
	CString szQuery;
	CString szTable;

	// get current date time
	COleDateTime date = COleDateTime::GetCurrentTime();
	szDate = date.Format( _T("%Y-%m-%d %H:%M:%S") );

	// set query
	szQuery.Format( _T("INSERT INTO external_ill_history (staff_id, user_id, date_out, description, link) VALUES (%I64d, %I64d, '%s', '%s', '%s')"),
		nStaffID, nUserID, szDate, ES(szDescription), ES(szLink) );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetEmptyRequest(REQUEST_SET *pRequest)
{
	// request
	pRequest->m_id = 0;
	pRequest->m_staff_id = 0;
	pRequest->m_user_id = 0;
	pRequest->m_date_in.SetDateTime(0, 0, 0, 0, 0, 0);
	pRequest->m_date_hold.SetDateTime(0, 0, 0, 0, 0, 0);
	pRequest->m_date_out.SetDateTime(0, 0, 0, 0, 0, 0);
	pRequest->m_status_id = 0;
	pRequest->m_collection_id = 0;
	pRequest->m_library_id = 0;
	pRequest->m_atlibrary = 0;
	pRequest->m_verified_id = 0;
	pRequest->m_step_id = 0;
	pRequest->m_request = _T("");
	pRequest->m_instructions = _T("");
	pRequest->m_information = _T("");
	pRequest->m_notes = _T("");
	pRequest->m_uid = _T("");
	pRequest->m_title = _T("");
	pRequest->m_source = _T("");
	pRequest->m_priority_id = 0;
	pRequest->m_method_id = 0;
	pRequest->m_ill_id = 0;
	pRequest->m_file = _T("");
	pRequest->m_viewed = 0;
	pRequest->m_redirected = 0;
	pRequest->m_touched = 0;
	pRequest->m_webcheck = 0;
	pRequest->m_deleted = 0;
	pRequest->m_invoiced = 0;
	pRequest->m_type = 0;
	pRequest->m_level = 0;
	pRequest->m_project = _T("");
	pRequest->m_taxable = 0;
	pRequest->m_overdue = 0;
	pRequest->m_copyright = 0;
	pRequest->m_canceled = 0; 
	pRequest->m_resubmitted = 0;
	pRequest->m_finalized = 0;
	pRequest->m_referrals = 0;
	pRequest->m_manual = 0;
	pRequest->m_material = 0;
	pRequest->m_fee_copyright = 0.00;
	pRequest->m_fee_service = 0.00;
	pRequest->m_fee_misc = 0.00;
	pRequest->m_external_id = 0;
	pRequest->m_retain = 0;

	// future expansion
	return TRUE;
}

ULONG CMySQLEx::GetRequestSet(CArray<REQUEST_SET> *pRequest)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, staff_id, user_id, date_in, date_hold, date_out, status_id, collection_id, library_id, atlibrary, verified_id, step_id, \
					   request, instructions, information, notes, \
					   uid, title, source, priority_id, method_id, ill_id, file, viewed, redirected, touched, webcheck, deleted, \
					   invoiced, type, level, project, taxable, overdue, copyright, canceled, resubmitted, finalized, referrals, \
					   manual, material, \
					   fee_copyright, fee_service, fee_misc, \
					   external_id, location, link, retain \
					   FROM requests") );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	REQUEST_SET Request;
	// clear array
	pRequest->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		// request
		Request.m_id = GetSQLLong(row[nRow++]);		
		Request.m_staff_id = GetSQLLong(row[nRow++]);
		Request.m_user_id = GetSQLLong(row[nRow++]);
		Request.m_date_in.ParseDateTime(GetSQLString(row[nRow++]));
		Request.m_date_hold.ParseDateTime(GetSQLString(row[nRow++]));
		Request.m_date_out.ParseDateTime(GetSQLString(row[nRow++]));
		Request.m_status_id = GetSQLLong(row[nRow++]);
		Request.m_collection_id = GetSQLLong(row[nRow++]);
		Request.m_library_id = GetSQLLong(row[nRow++]);
		Request.m_atlibrary = GetSQLLong(row[nRow++]);
		Request.m_verified_id = GetSQLLong(row[nRow++]);
		Request.m_step_id = GetSQLLong(row[nRow++]);
		Request.m_request = GetSQLString(row[nRow++]);
		Request.m_instructions = GetSQLString(row[nRow++]);
		Request.m_information = GetSQLString(row[nRow++]);
		Request.m_notes = GetSQLString(row[nRow++]);
		Request.m_uid = GetSQLString(row[nRow++]);
		Request.m_title = GetSQLString(row[nRow++]);
		Request.m_source = GetSQLString(row[nRow++]);
		Request.m_priority_id = GetSQLLong(row[nRow++]);
		Request.m_method_id = GetSQLLong(row[nRow++]);
		Request.m_ill_id = GetSQLLong(row[nRow++]);
		Request.m_file = GetSQLString(row[nRow++]);
		Request.m_viewed = GetSQLLong(row[nRow++]);
		Request.m_redirected = GetSQLLong(row[nRow++]);
		Request.m_touched = GetSQLLong(row[nRow++]);
		Request.m_webcheck = GetSQLLong(row[nRow++]);
		Request.m_deleted = GetSQLLong(row[nRow++]);
		Request.m_invoiced = GetSQLLong(row[nRow++]);
		Request.m_type = GetSQLLong(row[nRow++]);
		Request.m_level = GetSQLLong(row[nRow++]);
		Request.m_project = GetSQLString(row[nRow++]);
		Request.m_taxable = GetSQLLong(row[nRow++]);
		Request.m_overdue = GetSQLLong(row[nRow++]);
		Request.m_copyright = GetSQLLong(row[nRow++]);
		Request.m_canceled = GetSQLLong(row[nRow++]);
		Request.m_resubmitted = GetSQLLong(row[nRow++]);
		Request.m_finalized = GetSQLLong(row[nRow++]);
		Request.m_referrals = GetSQLLong(row[nRow++]);
		Request.m_manual = GetSQLLong(row[nRow++]);
		Request.m_material = GetSQLLong(row[nRow++]);
		Request.m_fee_copyright = GetSQLDouble(row[nRow++]);
		Request.m_fee_service = GetSQLDouble(row[nRow++]);
		Request.m_fee_misc = GetSQLDouble(row[nRow++]);
		Request.m_external_id = GetSQLLong(row[nRow++]);
		Request.m_location = GetSQLString(row[nRow++]);
		Request.m_link = GetSQLString(row[nRow++]);
		Request.m_retain = GetSQLLong(row[nRow++]);

		// insert
		pRequest->Add( Request );
	}
	// get count
	ULONG nCount = (ULONG)mysql_num_rows( res );
	mysql_free_result( res );

	Close();

	return nCount;
}

ULONG CMySQLEx::GetRequestSet(CArray<REQUEST_SET> *pRequest, LPCTSTR lpszSort, LPCTSTR lpszFilter, LPCTSTR lpszSearch, LONG nLimit, LONG nRange)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	CString szSort = lpszSort;
	CString szFilter = lpszFilter;
	CString szSearch = lpszSearch;

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, staff_id, user_id, date_in, date_hold, date_out, status_id, collection_id, library_id, atlibrary, verified_id, step_id, \
					   request, instructions, information, notes, \
					   uid, title, source, priority_id, method_id, ill_id, file, viewed, redirected, touched, webcheck, deleted, \
					   invoiced, type, level, project, taxable, overdue, copyright, canceled, resubmitted, finalized, referrals, \
					   manual, material, \
					   fee_copyright, fee_service, fee_misc, \
					   external_id, location, link, retain \
					   FROM requests") );

	// set search
	if( !szSearch.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE id LIKE '%%%s%%'"), szQuery, ES(szSearch) );
		szQuery = szTemp;
	}

	// set filter
	if( !szFilter.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE %s"), szQuery, szFilter );
		szQuery = szTemp;
	}

	// add sort
	if( !szSort.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s ORDER BY %s"), szQuery, ES(szSort) );
		szQuery = szTemp;
	}

	// add limit
	if( nLimit >= 0 )
	{
		CString szTemp;
		if( nRange > 2 )
			szTemp.Format( _T("%s LIMIT %ld,%ld"), szQuery, nLimit, nRange );
		else
			szTemp.Format( _T("%s LIMIT %ld"), szQuery, nLimit );
		szQuery = szTemp;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	REQUEST_SET Request;
	// clear array
	pRequest->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		// request
		Request.m_id = GetSQLLong(row[nRow++]);
		Request.m_staff_id = GetSQLLong(row[nRow++]);
		Request.m_user_id = GetSQLLong(row[nRow++]);
		Request.m_date_in.ParseDateTime(GetSQLString(row[nRow++]));
		Request.m_date_hold.ParseDateTime(GetSQLString(row[nRow++]));
		Request.m_date_out.ParseDateTime(GetSQLString(row[nRow++]));
		Request.m_status_id = GetSQLLong(row[nRow++]);
		Request.m_collection_id = GetSQLLong(row[nRow++]);
		Request.m_library_id = GetSQLLong(row[nRow++]);
		Request.m_atlibrary = GetSQLLong(row[nRow++]);
		Request.m_verified_id = GetSQLLong(row[nRow++]);
		Request.m_step_id = GetSQLLong(row[nRow++]);
		Request.m_request = GetSQLString(row[nRow++]);
		Request.m_instructions = GetSQLString(row[nRow++]);
		Request.m_information = GetSQLString(row[nRow++]);
		Request.m_notes = GetSQLString(row[nRow++]);
		Request.m_uid = GetSQLString(row[nRow++]);
		Request.m_title = GetSQLString(row[nRow++]);
		Request.m_source = GetSQLString(row[nRow++]);
		Request.m_priority_id = GetSQLLong(row[nRow++]);
		Request.m_method_id = GetSQLLong(row[nRow++]);
		Request.m_ill_id = GetSQLLong(row[nRow++]);
		Request.m_file = GetSQLString(row[nRow++]);
		Request.m_viewed = GetSQLLong(row[nRow++]);
		Request.m_redirected = GetSQLLong(row[nRow++]);
		Request.m_touched = GetSQLLong(row[nRow++]);
		Request.m_webcheck = GetSQLLong(row[nRow++]);
		Request.m_deleted = GetSQLLong(row[nRow++]);
		Request.m_invoiced = GetSQLLong(row[nRow++]);
		Request.m_type = GetSQLLong(row[nRow++]);
		Request.m_level = GetSQLLong(row[nRow++]);
		Request.m_project = GetSQLString(row[nRow++]);
		Request.m_taxable = GetSQLLong(row[nRow++]);
		Request.m_overdue = GetSQLLong(row[nRow++]);
		Request.m_copyright = GetSQLLong(row[nRow++]);
		Request.m_canceled = GetSQLLong(row[nRow++]);
		Request.m_resubmitted = GetSQLLong(row[nRow++]);
		Request.m_finalized = GetSQLLong(row[nRow++]);
		Request.m_referrals = GetSQLLong(row[nRow++]);
		Request.m_manual = GetSQLLong(row[nRow++]);
		Request.m_material = GetSQLLong(row[nRow++]);
		Request.m_fee_copyright = GetSQLDouble(row[nRow++]);
		Request.m_fee_service = GetSQLDouble(row[nRow++]);
		Request.m_fee_misc = GetSQLDouble(row[nRow++]);
		Request.m_external_id = GetSQLLong(row[nRow++]);
		Request.m_location = GetSQLString(row[nRow++]);
		Request.m_link = GetSQLString(row[nRow++]);
		Request.m_retain = GetSQLLong(row[nRow++]);

		// insert
		pRequest->Add( Request );
	}
	// get count
	ULONG nCount = (ULONG)mysql_num_rows( res );
	mysql_free_result( res );

	Close();

	return nCount;
}

BOOL CMySQLEx::GetRequestSet(REQUEST_SET *pRequest, LONGLONG nID)
{
	USES_CONVERSION;

	ASSERT( nID >= 0 );
	// init no ID
	if( nID == 0 )
	{
		InitRequestSet( pRequest );
		return TRUE;
	}

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, staff_id, user_id, date_in, date_hold, date_out, status_id, collection_id, library_id, atlibrary, verified_id, step_id, \
					   request, instructions, information, notes, \
					   uid, title, source, priority_id, method_id, ill_id, file, viewed, redirected, touched, webcheck, deleted, \
					   invoiced, type, level, project, taxable, overdue, copyright, canceled, resubmitted, finalized, referrals, \
					   manual, material, \
					   fee_copyright, fee_service, fee_misc, \
					   external_id, location, link, retain \
					   FROM requests") );

	// set search
	if( nID > 0 )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE id=%I64d"), szQuery, nID );
		szQuery = szTemp;
	}

	// set -1 for not found
	pRequest->m_id = -1;

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		// request
		pRequest->m_id = GetSQLLong(row[nRow++]);
		pRequest->m_staff_id = GetSQLLong(row[nRow++]);
		pRequest->m_user_id = GetSQLLong(row[nRow++]);
		pRequest->m_date_in.ParseDateTime(GetSQLString(row[nRow++]));
		pRequest->m_date_hold.ParseDateTime(GetSQLString(row[nRow++]));
		pRequest->m_date_out.ParseDateTime(GetSQLString(row[nRow++]));
		pRequest->m_status_id = GetSQLLong(row[nRow++]);
		pRequest->m_collection_id = GetSQLLong(row[nRow++]);
		pRequest->m_library_id = GetSQLLong(row[nRow++]);
		pRequest->m_atlibrary = GetSQLLong(row[nRow++]);
		pRequest->m_verified_id = GetSQLLong(row[nRow++]);
		pRequest->m_step_id = GetSQLLong(row[nRow++]);
		pRequest->m_request = GetSQLString(row[nRow++]);
		pRequest->m_instructions = GetSQLString(row[nRow++]);
		pRequest->m_information = GetSQLString(row[nRow++]);
		pRequest->m_notes = GetSQLString(row[nRow++]);
		pRequest->m_uid = GetSQLString(row[nRow++]);
		pRequest->m_title = GetSQLString(row[nRow++]);
		pRequest->m_source = GetSQLString(row[nRow++]);
		pRequest->m_priority_id = GetSQLLong(row[nRow++]);
		pRequest->m_method_id = GetSQLLong(row[nRow++]);
		pRequest->m_ill_id = GetSQLLong(row[nRow++]);
		pRequest->m_file = GetSQLString(row[nRow++]);
		pRequest->m_viewed = GetSQLLong(row[nRow++]);
		pRequest->m_redirected = GetSQLLong(row[nRow++]);
		pRequest->m_touched = GetSQLLong(row[nRow++]);
		pRequest->m_webcheck = GetSQLLong(row[nRow++]);
		pRequest->m_deleted = GetSQLLong(row[nRow++]);
		pRequest->m_invoiced = GetSQLLong(row[nRow++]);
		pRequest->m_type = GetSQLLong(row[nRow++]);
		pRequest->m_level = GetSQLLong(row[nRow++]);
		pRequest->m_project = GetSQLString(row[nRow++]);
		pRequest->m_taxable = GetSQLLong(row[nRow++]);
		pRequest->m_overdue = GetSQLLong(row[nRow++]);
		pRequest->m_copyright = GetSQLLong(row[nRow++]);
		pRequest->m_canceled = GetSQLLong(row[nRow++]);
		pRequest->m_resubmitted = GetSQLLong(row[nRow++]);
		pRequest->m_finalized = GetSQLLong(row[nRow++]);
		pRequest->m_referrals = GetSQLLong(row[nRow++]);
		pRequest->m_manual = GetSQLLong(row[nRow++]);
		pRequest->m_material = GetSQLLong(row[nRow++]);
		pRequest->m_fee_copyright = GetSQLDouble(row[nRow++]);
		pRequest->m_fee_service = GetSQLDouble(row[nRow++]);
		pRequest->m_fee_misc = GetSQLDouble(row[nRow++]);
		pRequest->m_external_id = GetSQLLong(row[nRow++]);
		pRequest->m_location = GetSQLString(row[nRow++]);
		pRequest->m_link = GetSQLString(row[nRow++]);
		pRequest->m_retain = GetSQLLong(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::GetRequestSet(REQUEST_SET *pRequest, CString szEmail)
{
	USES_CONVERSION;

	ASSERT( !szEmail.IsEmpty() );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery.Format( _T("SELECT id, staff_id, user_id, date_in, date_hold, date_out, status_id, collection_id, library_id, atlibrary, verified_id, step_id, \
					   request, instructions, information, notes, \
					   uid, title, source, priority_id, method_id, ill_id, file, viewed, redirected, touched, webcheck, deleted, \
					   invoiced, type, level, project, taxable, overdue, copyright, canceled, resubmitted, finalized, referrals, \
					   manual, material, \
					   fee_copyright, fee_service, fee_misc, \
					   external_id, location, link, retain \
					   FROM requests") );

	// set search
	if( !szEmail.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE email='%s'"), szQuery, ES(szEmail) );
		szQuery = szTemp;
	}

	// set -1 for not found
	pRequest->m_id = -1;

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		// request
		pRequest->m_id = GetSQLLong(row[nRow++]);
		pRequest->m_staff_id = GetSQLLong(row[nRow++]);
		pRequest->m_user_id = GetSQLLong(row[nRow++]);
		pRequest->m_date_in.ParseDateTime(GetSQLString(row[nRow++]));
		pRequest->m_date_hold.ParseDateTime(GetSQLString(row[nRow++]));
		pRequest->m_date_out.ParseDateTime(GetSQLString(row[nRow++]));
		pRequest->m_status_id = GetSQLLong(row[nRow++]);
		pRequest->m_collection_id = GetSQLLong(row[nRow++]);
		pRequest->m_library_id = GetSQLLong(row[nRow++]);
		pRequest->m_atlibrary = GetSQLLong(row[nRow++]);
		pRequest->m_verified_id = GetSQLLong(row[nRow++]);
		pRequest->m_step_id = GetSQLLong(row[nRow++]);
		pRequest->m_request = GetSQLString(row[nRow++]);
		pRequest->m_instructions = GetSQLString(row[nRow++]);
		pRequest->m_information = GetSQLString(row[nRow++]);
		pRequest->m_notes = GetSQLString(row[nRow++]);
		pRequest->m_uid = GetSQLString(row[nRow++]);
		pRequest->m_title = GetSQLString(row[nRow++]);
		pRequest->m_source = GetSQLString(row[nRow++]);
		pRequest->m_priority_id = GetSQLLong(row[nRow++]);
		pRequest->m_method_id = GetSQLLong(row[nRow++]);
		pRequest->m_ill_id = GetSQLLong(row[nRow++]);
		pRequest->m_file = GetSQLString(row[nRow++]);
		pRequest->m_viewed = GetSQLLong(row[nRow++]);
		pRequest->m_redirected = GetSQLLong(row[nRow++]);
		pRequest->m_touched = GetSQLLong(row[nRow++]);
		pRequest->m_webcheck = GetSQLLong(row[nRow++]);
		pRequest->m_deleted = GetSQLLong(row[nRow++]);
		pRequest->m_invoiced = GetSQLLong(row[nRow++]);
		pRequest->m_type = GetSQLLong(row[nRow++]);
		pRequest->m_level = GetSQLLong(row[nRow++]);
		pRequest->m_project = GetSQLString(row[nRow++]);
		pRequest->m_taxable = GetSQLLong(row[nRow++]);
		pRequest->m_overdue = GetSQLLong(row[nRow++]);
		pRequest->m_copyright = GetSQLLong(row[nRow++]);
		pRequest->m_canceled = GetSQLLong(row[nRow++]);
		pRequest->m_resubmitted = GetSQLLong(row[nRow++]);
		pRequest->m_finalized = GetSQLLong(row[nRow++]);
		pRequest->m_referrals = GetSQLLong(row[nRow++]);
		pRequest->m_manual = GetSQLLong(row[nRow++]);
		pRequest->m_material = GetSQLLong(row[nRow++]);
		pRequest->m_fee_copyright = GetSQLDouble(row[nRow++]);
		pRequest->m_fee_service = GetSQLDouble(row[nRow++]);
		pRequest->m_fee_misc = GetSQLDouble(row[nRow++]);
		pRequest->m_external_id = GetSQLLong(row[nRow++]);
		pRequest->m_location = GetSQLString(row[nRow++]);
		pRequest->m_link = GetSQLString(row[nRow++]);
		pRequest->m_retain = GetSQLLong(row[nRow++]);
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}

BOOL CMySQLEx::SetRequestSet(REQUEST_SET *pRequest, LONGLONG nID, BOOL bInsert)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	// check request before saving
	if( pRequest->m_user_id < 0 )
	{
		AfxMessageBox( _T("ERROR: No user assigned to request.") );
		return FALSE;
	}

	// if id < 0 then inserting
	if( nID > 0 )
		bInsert = TRUE;

	CString szQuery;
	CString szDate;
	MYSQL_RES *res;
	MYSQL_ROW row;

	// check for existing
	if( bInsert )
	{
		szQuery.Format( _T("SELECT id FROM requests WHERE id=%I64d"), nID );
		res = QueryForRow( szQuery, &row );
		ULONG nCount = (ULONG)mysql_num_rows( res );
		mysql_free_result( res );

		if( nCount <= 0 )
		{
			szQuery.Format( _T("INSERT INTO requests \
							   	(staff_id, user_id,\
								date_in, date_hold, date_out,\
								status_id, collection_id, library_id, atlibrary, verified_id,\
								step_id, request, instructions, information, notes, uid, title,\
								source, priority_id, method_id, ill_id, file, viewed, redirected,\
								touched, webcheck, deleted, invoiced, type, level, project,\
								taxable, overdue, copyright, canceled, resubmitted, finalized, referrals,\
								manual, material, fee_copyright, fee_service, fee_misc, external_id,\
								location, link, retain)\
								VALUES\
								(%ld, %ld,\
								'%s', '%s', '%s',\
								%d, %d, %d, %d, %ld,\
								%d, '%s', '%s', '%s', '%s', '%s', '%s',\
								'%s', %d, %d, %d, '%s', %d, %d,\
								%d, %d, %d, %d, %d, %d, '%s',\
								%d, %d, %d, %d, %d, %d, %d,\
								%d, %d, %f, %f, %f, %ld,\
								'%s', '%s', %d)"),
								pRequest->m_staff_id, pRequest->m_user_id,
								pRequest->m_date_in.Format( _T("%Y-%m-%d %H:%M:%S") ), pRequest->m_date_hold.Format( _T("%Y-%m-%d %H:%M:%S") ), pRequest->m_date_out.Format( _T("%Y-%m-%d %H:%M:%S") ),
								pRequest->m_status_id, pRequest->m_collection_id, pRequest->m_library_id, pRequest->m_atlibrary, pRequest->m_verified_id,
								pRequest->m_step_id, ES(pRequest->m_request), ES(pRequest->m_instructions), ES(pRequest->m_information), ES(pRequest->m_notes), ES(pRequest->m_uid), ES(pRequest->m_title),
								ES(pRequest->m_source), pRequest->m_priority_id, pRequest->m_method_id, pRequest->m_ill_id, ES(pRequest->m_file), pRequest->m_viewed, pRequest->m_redirected,
								pRequest->m_touched, pRequest->m_webcheck, pRequest->m_deleted, pRequest->m_invoiced, pRequest->m_type, pRequest->m_level, ES(pRequest->m_project),
								pRequest->m_taxable, pRequest->m_overdue, pRequest->m_copyright, pRequest->m_canceled, pRequest->m_resubmitted, pRequest->m_finalized, pRequest->m_referrals,
								pRequest->m_manual, pRequest->m_material, pRequest->m_fee_copyright, pRequest->m_fee_service, pRequest->m_fee_misc, pRequest->m_external_id,
								ES(pRequest->m_location), ES(pRequest->m_link), pRequest->m_retain );
		}
		else
		{
			bInsert = FALSE;
		}
	}

	// must be a UPDATE
	if( !bInsert )
	{
		szQuery.Format( _T("UPDATE requests SET \
						   staff_id=%ld, user_id=%ld,\
						   date_in='%s', date_hold='%s', date_out='%s',\
						   status_id=%d, collection_id=%d, library_id=%d, atlibrary=%d, verified_id=%ld,\
						   step_id=%d, request='%s', instructions='%s', information='%s', notes='%s', uid='%s', title='%s',\
						   source='%s', priority_id=%d, method_id=%d, ill_id=%d, file='%s', viewed=%d, redirected=%d,\
						   touched=%d, webcheck=%d, deleted=%d, invoiced=%d, type=%d, level=%d, project='%s',\
						   taxable=%d, overdue=%d, copyright=%d, canceled=%d, resubmitted=%d, finalized=%d, referrals=%d,\
						   manual=%d, material=%d, fee_copyright=%f, fee_service=%f, fee_misc=%f, external_id=%ld,\
						   location='%s', link='%s', retain=%d"),
						   pRequest->m_staff_id, pRequest->m_user_id,
						   pRequest->m_date_in.Format( _T("%Y-%m-%d %H:%M:%S") ), pRequest->m_date_hold.Format( _T("%Y-%m-%d %H:%M:%S") ), pRequest->m_date_out.Format( _T("%Y-%m-%d %H:%M:%S") ),
						   pRequest->m_status_id, pRequest->m_collection_id, pRequest->m_library_id, pRequest->m_atlibrary, pRequest->m_verified_id,
						   pRequest->m_step_id, ES(pRequest->m_request), ES(pRequest->m_instructions), ES(pRequest->m_information), ES(pRequest->m_notes), ES(pRequest->m_uid), ES(pRequest->m_title),
						   ES(pRequest->m_source), pRequest->m_priority_id, pRequest->m_method_id, pRequest->m_ill_id, ES(pRequest->m_file), pRequest->m_viewed, pRequest->m_redirected,
						   pRequest->m_touched, pRequest->m_webcheck, pRequest->m_deleted, pRequest->m_invoiced, pRequest->m_type, pRequest->m_level, ES(pRequest->m_project),
						   pRequest->m_taxable, pRequest->m_overdue, pRequest->m_copyright, pRequest->m_canceled, pRequest->m_resubmitted, pRequest->m_finalized, pRequest->m_referrals,
						   pRequest->m_manual, pRequest->m_material, pRequest->m_fee_copyright, pRequest->m_fee_service, pRequest->m_fee_misc, pRequest->m_external_id,
						   ES(pRequest->m_location), ES(pRequest->m_link), pRequest->m_retain );
		// set ID
		CString szWhere;
		if( nID >= 0 )
			szWhere.Format( _T(" WHERE id=%I64d"), nID );
		else
			szWhere.Format( _T(" WHERE id=%ld"), pRequest->m_id );

		// add where
		szQuery += szWhere;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		CString szMessage;
		szMessage.Format( _T("SetRequestSet: %s"), szError );
		AfxMessageBox( szMessage );
		Close();
		return FALSE;
	}

	// get inserted ID
	if( bInsert )
	{
		LONGLONG nInsertID = mysql_insert_id( &m_mysql );
		pRequest->m_id = (LONG)nInsertID;
	}

	// close local
	Close();

	// update external request
	if( pRequest->m_external_id > 0 )
		SetExternalRequestSet( pRequest, pRequest->m_external_id );

	return TRUE;
}

BOOL CMySQLEx::SetExternalRequestSet(REQUEST_SET *pRequest, LONGLONG nID, BOOL bInsert)
{
	USES_CONVERSION;

	// get external user ID
	if( bInsert )
	{
		// get user
		USER_SET User;
		if( !GetUserSet( &User, pRequest->m_user_id ))
			return FALSE;
		// convert it to external
		pRequest->m_user_id = User.m_external_id;
	}

	// connect
	if( !Connect( TRUE ) )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;
	CString szDate;
	MYSQL_RES *res;
	MYSQL_ROW row;
	
	// check for existing
	if( bInsert )
	{
		szQuery.Format( _T("SELECT id FROM requests WHERE id=%I64d"), nID );
		res = QueryForRow( szQuery, &row );
		ULONG nCount = (ULONG)mysql_num_rows( res );
		mysql_free_result( res );

		if( nCount <= 0 )
		{
			// use external user ID

			// build query
			szQuery.Format( _T("INSERT INTO requests \
							   	(staff_id, user_id,\
								date_in, date_hold, date_out,\
								status_id, collection_id, library_id, atlibrary, verified_id,\
								step_id, request, instructions, information, notes, uid, title,\
								source, priority_id, method_id, ill_id, file, viewed, redirected,\
								touched, webcheck, deleted, invoiced, type, level, project,\
								taxable, overdue, copyright, canceled, resubmitted, finalized, referrals,\
								manual, material, fee_copyright, fee_service, fee_misc,\
								external_id, location, link, retain)\
								VALUES\
								(%ld, %ld,\
								'%s', '%s', '%s',\
								%d, %d, %d, %d, %ld,\
								%d, '%s', '%s', '%s', '%s', '%s', '%s',\
								'%s', %d, %d, %d, '%s', %d, %d,\
								%d, %d, %d, %d, %d, %d, '%s',\
								%d, %d, %d, %d, %d, %d, %d,\
								%d, %d, %f, %f, %f,\
								%d, '%s', '%s', %d)"),
								pRequest->m_staff_id, pRequest->m_user_id,
								pRequest->m_date_in.Format( _T("%Y-%m-%d %H:%M:%S") ), pRequest->m_date_hold.Format( _T("%Y-%m-%d %H:%M:%S") ), pRequest->m_date_out.Format( _T("%Y-%m-%d %H:%M:%S") ),
								pRequest->m_status_id, pRequest->m_collection_id, pRequest->m_library_id, pRequest->m_atlibrary, pRequest->m_verified_id,
								pRequest->m_step_id, ES(pRequest->m_request), ES(pRequest->m_instructions), ES(pRequest->m_information), ES(pRequest->m_notes), ES(pRequest->m_uid), ES(pRequest->m_title),
								ES(pRequest->m_source), pRequest->m_priority_id, pRequest->m_method_id, pRequest->m_ill_id, ES(pRequest->m_file), pRequest->m_viewed, pRequest->m_redirected,
								pRequest->m_touched, pRequest->m_webcheck, pRequest->m_deleted, pRequest->m_invoiced, pRequest->m_type, pRequest->m_level, ES(pRequest->m_project),
								pRequest->m_taxable, pRequest->m_overdue, pRequest->m_copyright, pRequest->m_canceled, pRequest->m_resubmitted, pRequest->m_finalized, pRequest->m_referrals,
								pRequest->m_manual, pRequest->m_material, pRequest->m_fee_copyright, pRequest->m_fee_service, pRequest->m_fee_misc,
								pRequest->m_external_id, ES(pRequest->m_location), ES(pRequest->m_link), pRequest->m_retain );
		}
		else
		{
			bInsert = FALSE;
		}
	}

	// must be an UPDATE
	if( !bInsert )
	{
		// do not change user_id=%ld or external_id=%ld

		// build query
		szQuery.Format( _T("UPDATE requests SET \
						   staff_id=%ld, \
						   date_in='%s', date_hold='%s', date_out='%s',\
						   status_id=%d, collection_id=%d, library_id=%d, atlibrary=%d, verified_id=%ld,\
						   step_id=%d, request='%s', instructions='%s', information='%s', notes='%s', uid='%s', title='%s',\
						   source='%s', priority_id=%d, method_id=%d, ill_id=%d, file='%s', viewed=%d, redirected=%d,\
						   touched=%d, webcheck=%d, deleted=%d, invoiced=%d, type=%d, level=%d, project='%s',\
						   taxable=%d, overdue=%d, copyright=%d, canceled=%d, resubmitted=%d, finalized=%d, referrals=%d,\
						   manual=%d, material=%d, fee_copyright=%f, fee_service=%f, fee_misc=%f,\
						   external_id=%d, location='%s', link='%s', retain=%d"),
						   pRequest->m_staff_id,
						   pRequest->m_date_in.Format( _T("%Y-%m-%d %H:%M:%S") ), pRequest->m_date_hold.Format( _T("%Y-%m-%d %H:%M:%S") ), pRequest->m_date_out.Format( _T("%Y-%m-%d %H:%M:%S") ),
						   pRequest->m_status_id, pRequest->m_collection_id, pRequest->m_library_id, pRequest->m_atlibrary, pRequest->m_verified_id,
						   pRequest->m_step_id, ES(pRequest->m_request), ES(pRequest->m_instructions), ES(pRequest->m_information), ES(pRequest->m_notes), ES(pRequest->m_uid), ES(pRequest->m_title),
						   ES(pRequest->m_source), pRequest->m_priority_id, pRequest->m_method_id, pRequest->m_ill_id, ES(pRequest->m_file), pRequest->m_viewed, pRequest->m_redirected,
						   pRequest->m_touched, pRequest->m_webcheck, pRequest->m_deleted, pRequest->m_invoiced, pRequest->m_type, pRequest->m_level, ES(pRequest->m_project),
						   pRequest->m_taxable, pRequest->m_overdue, pRequest->m_copyright, pRequest->m_canceled, pRequest->m_resubmitted, pRequest->m_finalized, pRequest->m_referrals,
						   pRequest->m_manual, pRequest->m_material, pRequest->m_fee_copyright, pRequest->m_fee_service, pRequest->m_fee_misc,
						   pRequest->m_external_id, ES(pRequest->m_location), ES(pRequest->m_link), pRequest->m_retain );
		// set ID
		CString szWhere;
		if( nID >= 0 )
			szWhere.Format( _T(" WHERE id=%I64d"), nID );
		else
			szWhere.Format( _T(" WHERE id=%ld"), pRequest->m_id );

		// add where
		szQuery += szWhere;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}

	// get inserted ID
	if( bInsert )
	{
		LONGLONG nInsertID = mysql_insert_id( &m_mysql );
		pRequest->m_id = (LONG)nInsertID;
	}

	Close();
	return TRUE;
}

BOOL CMySQLEx::RemoveRequest(LONGLONG nID)
{
	USES_CONVERSION;

	// get request
	REQUEST_SET Request;
	GetRequestSet( &Request, nID );

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return FALSE;
	}

	CString szQuery;

	// set table
	szQuery.Format( _T("DELETE FROM requests WHERE id=%ld"), Request.m_id );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		Close();
		return FALSE;
	}
	Close();

	// check for external 
	if( Request.m_external_id > 0 )
	{
		// connect
		if( !Connect( TRUE ) )
		{
			ASSERT( FALSE );
			return FALSE;
		}

		CString szQuery;

		// set table
		szQuery.Format( _T("DELETE FROM requests WHERE id=%ld"), Request.m_external_id );

		// query
		if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
		{
			CString szError(mysql_error( &m_mysql ));
			AfxMessageBox( szError );
			Close();
			return FALSE;
		}
		Close();
	}

	return TRUE;
}

BOOL CMySQLEx::SetFullRequest(ARTICLE_REQUEST *pRequest)
{
	BOOL bRetVal = TRUE;
	USER_SET User;
	REQUEST_SET Request;

	// user
	User.m_id = pRequest->m_users_id;
	User.m_level = pRequest->m_users_level;
	User.m_name = pRequest->m_users_name;
	User.m_pwd = pRequest->m_users_pwd;
	User.m_email = pRequest->m_users_email;
	User.m_company = pRequest->m_users_company;
	User.m_phone = pRequest->m_users_phone;
	User.m_fax= pRequest->m_users_fax;
	User.m_shipto = pRequest->m_users_shipto;
	User.m_address1 = pRequest->m_users_address1;
	User.m_address2 = pRequest->m_users_address2;
	User.m_city = pRequest->m_users_city;
	User.m_state = pRequest->m_users_state;
	User.m_zipcode = pRequest->m_users_zipcode;
	User.m_country = pRequest->m_users_country;
	User.m_billto = pRequest->m_users_billto;
	User.m_billaddress1 = pRequest->m_users_billaddress1;
	User.m_billaddress2 = pRequest->m_users_billaddress2;
	User.m_billcity = pRequest->m_users_billcity;
	User.m_billstate = pRequest->m_users_billstate;
	User.m_billzipcode = pRequest->m_users_billzipcode;
	User.m_billcountry = pRequest->m_users_billcountry;
	User.m_altemail1 = pRequest->m_users_altemail1;
	User.m_altemail2 = pRequest->m_users_altemail2;
	User.m_altemail3 = pRequest->m_users_altemail3;
	User.m_altemail4 = pRequest->m_users_altemail4;
	User.m_altemail5 = pRequest->m_users_altemail5;
	User.m_library_id = pRequest->m_users_library_id;
	User.m_date_join = pRequest->m_users_date_join;
	User.m_date_login = pRequest->m_users_date_login;
	User.m_taxable = pRequest->m_users_taxable;
	User.m_fee_max = pRequest->m_users_fee_max;
	User.m_activation_code = pRequest->m_users_activation_code;
	User.m_activated = pRequest->m_users_activated;
	User.m_notify = pRequest->m_users_notify;
	User.m_type = pRequest->m_users_type;
	User.m_method_id = pRequest->m_users_method_id;
	User.m_referrals = pRequest->m_users_referrals;
	User.m_copyright = pRequest->m_users_copyright;
	User.m_account_number = pRequest->m_users_account_number;
	User.m_shipping_number = pRequest->m_users_shipping_number;
	User.m_external = pRequest->m_users_external;
	User.m_notes = pRequest->m_users_notes;
	User.m_external_id = pRequest->m_users_external_id;

	// request
	Request.m_id = pRequest->m_id;
	Request.m_user_id = pRequest->m_user_id;
	Request.m_staff_id = pRequest->m_staff_id;
	Request.m_date_in = pRequest->m_date_in;
	Request.m_date_hold = pRequest->m_date_hold;
	Request.m_date_out = pRequest->m_date_out;
	Request.m_status_id = pRequest->m_status_id;
	Request.m_collection_id = pRequest->m_collection_id;
	Request.m_library_id = pRequest->m_library_id;
	Request.m_atlibrary = pRequest->m_atlibrary;
	Request.m_verified_id = pRequest->m_verified_id;
	Request.m_step_id = pRequest->m_step_id;
	Request.m_request = pRequest->m_request;
	Request.m_instructions = pRequest->m_instructions;
	Request.m_information = pRequest->m_information;
	Request.m_notes = pRequest->m_notes;
	Request.m_uid = pRequest->m_uid;
	Request.m_title = pRequest->m_title;
	Request.m_source = pRequest->m_source;
	Request.m_priority_id = pRequest->m_priority_id;
	Request.m_method_id = pRequest->m_method_id;
	Request.m_ill_id = pRequest->m_ill_id;
	Request.m_file = pRequest->m_file;
	Request.m_viewed = pRequest->m_viewed;
	Request.m_redirected = pRequest->m_redirected;
	Request.m_touched = pRequest->m_touched;
	Request.m_webcheck = pRequest->m_webcheck;
	Request.m_deleted = pRequest->m_deleted;
	Request.m_invoiced = pRequest->m_invoiced;
	Request.m_type = pRequest->m_type;
	Request.m_level = pRequest->m_level;
	Request.m_project = pRequest->m_project;
	Request.m_taxable = pRequest->m_taxable;
	Request.m_overdue = pRequest->m_overdue;
	Request.m_copyright = pRequest->m_copyright;
	Request.m_canceled = pRequest->m_canceled;
	Request.m_resubmitted = pRequest->m_resubmitted;
	Request.m_finalized = pRequest->m_finalized;
	Request.m_referrals = pRequest->m_referrals;
	Request.m_manual = pRequest->m_manual;
	Request.m_material = pRequest->m_material;
	Request.m_fee_copyright = pRequest->m_fee_copyright;
	Request.m_fee_service = pRequest->m_fee_service;
	Request.m_fee_misc = pRequest->m_fee_misc;
	Request.m_external_id = pRequest->m_external_id;
	Request.m_location = pRequest->m_location;
	Request.m_link = pRequest->m_link;
	Request.m_retain = pRequest->m_retain;

	// set tables
	if( !SetUserSet( &User, User.m_id ))
		bRetVal = FALSE;
	if( !SetRequestSet( &Request, Request.m_id ))
		bRetVal = FALSE;

	return bRetVal;
}

ULONG CMySQLEx::GetRequestDisplaySet(CArray<REQUEST_DISPLAY_SET> *pRequest, LPCTSTR lpszSort, LPCTSTR lpszFilter, LPCTSTR lpszSearch, LONG nLimit, LONG nRange)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	CString szSort = lpszSort;
	CString szFilter = lpszFilter;
	CString szSearch = lpszSearch;

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	szQuery = _T("SELECT requests.id, requests.staff_id, requests.user_id, requests.external_id, \
				 requests.date_in, requests.date_hold, requests.date_out, requests.status_id, requests.library_id, \
				 requests.collection_id, requests.atlibrary, \
				 requests.step_id, requests.request, requests.source, requests.priority_id, requests.method_id, \
				 requests.touched, requests.webcheck, requests.invoiced, requests.project, requests.uid, requests.location, requests.link, requests.retain, \
				 users.email, users.company, users.address1, \
				 staff.name, staff.email \
				 FROM requests JOIN users ON requests.user_id=users.id JOIN staff ON requests.staff_id=staff.id");

	// set search
	if( !szSearch.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE id LIKE '%%%s%%'"), szQuery, ES(szSearch) );
		szQuery = szTemp;
	}

	// set filter
	if( !szFilter.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE %s"), szQuery, szFilter );
		szQuery = szTemp;
	}

	// add sort
	if( !szSort.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s ORDER BY %s"), szQuery, szSort );
		szQuery = szTemp;
	}

	// add limit
	if( nLimit > 0 )
	{
		CString szTemp;
		if( nRange >= 1 )
			szTemp.Format( _T("%s LIMIT %ld,%ld"), szQuery, nLimit, nRange );
		else
			szTemp.Format( _T("%s LIMIT %ld"), szQuery, nLimit );
		szQuery = szTemp;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	REQUEST_DISPLAY_SET Request;
	// clear array
	pRequest->RemoveAll();

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		// request
		Request.m_id = GetSQLLong(row[nRow++]);
		Request.m_staff_id = GetSQLLong(row[nRow++]);;
		Request.m_user_id = GetSQLLong(row[nRow++]);
		Request.m_external_id = GetSQLLong(row[nRow++]);
		Request.m_date_in.ParseDateTime(GetSQLString(row[nRow++]));
		Request.m_date_hold.ParseDateTime(GetSQLString(row[nRow++]));
		Request.m_date_out.ParseDateTime(GetSQLString(row[nRow++]));
		Request.m_status_id = GetSQLLong(row[nRow++]);
		Request.m_library_id = GetSQLLong(row[nRow++]);
		Request.m_collection_id = GetSQLLong(row[nRow++]);
		Request.m_atlibrary = GetSQLLong(row[nRow++]);
		Request.m_step_id = GetSQLLong(row[nRow++]);
		Request.m_request = GetSQLString(row[nRow++]);
		Request.m_source = GetSQLString(row[nRow++]);
		Request.m_priority_id = GetSQLLong(row[nRow++]);
		Request.m_method_id = GetSQLLong(row[nRow++]);
		Request.m_touched = GetSQLLong(row[nRow++]);
		Request.m_webcheck = GetSQLLong(row[nRow++]);
		Request.m_invoiced = GetSQLLong(row[nRow++]);
		Request.m_project = GetSQLString(row[nRow++]);
		Request.m_uid = GetSQLString(row[nRow++]);
		Request.m_location = GetSQLString(row[nRow++]);
		Request.m_link = GetSQLString(row[nRow++]);
		Request.m_retain = GetSQLLong(row[nRow++]);
		// user
		Request.m_users_email = GetSQLString(row[nRow++]);
		Request.m_users_company = GetSQLString(row[nRow++]);
		Request.m_users_address1 = GetSQLString(row[nRow++]);

		// insert
		pRequest->Add( Request );
	}
	// get count
	ULONG nCount = (ULONG)mysql_num_rows( res );
	mysql_free_result( res );

	Close();

	return nCount;
}

ULONG CMySQLEx::GetFullRequestMap(RequestMap *pRequestSet, LPCTSTR lpszSort, LPCTSTR lpszFilter, LONG nLimit, LONG nRange)
{
	USES_CONVERSION;

	CString szSort = lpszSort;
	CString szFilter = lpszFilter;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	// use source check
	if( appConfig.m_Options.bDisableSourceLinkCheck )
		szQuery = _T("SELECT requests.id, requests.staff_id, requests.user_id, requests.external_id, \
					 requests.date_in, requests.date_hold, requests.date_out, requests.status_id, requests.library_id, \
					 requests.collection_id, requests.atlibrary, \
					 requests.step_id, requests.request, requests.source, requests.priority_id, requests.method_id, \
					 requests.touched, requests.webcheck, requests.invoiced, requests.project, requests.uid, requests.location, requests.link, requests.retain, \
					 users.email, users.lan_id, users.company, users.address1 \
					 FROM requests LEFT JOIN users ON requests.user_id=users.id");
	else
		szQuery.Format( _T("SELECT requests.id, requests.staff_id, requests.user_id, requests.external_id, \
					 requests.date_in, requests.date_hold, requests.date_out, requests.status_id, requests.library_id, \
					 requests.collection_id, requests.atlibrary, \
					 requests.step_id, requests.request, requests.source, requests.priority_id, requests.method_id, \
					 requests.touched, requests.webcheck, requests.invoiced, requests.project, requests.uid, requests.location, requests.link, requests.retain, \
					 users.email, users.lan_id, users.company, users.address1, \
					 request_links.link, \
					 source_links.link, source_links.notes, source_links.material \
					 FROM requests \
					 LEFT JOIN users ON requests.user_id=users.id \
					 LEFT JOIN request_links ON requests.uid=request_links.uid \
					 LEFT JOIN source_links ON LEFT(requests.source, LOCATE('%s', requests.source) -1)=source_links.title"),
					 appConfig.m_SFX.szSourceEndString );

	
	// old source link query
	//LEFT JOIN source_links ON LEFT(requests.source, LOCATE('%s', requests.source) -1)=source_links.title"),

	// test 1
	//LEFT JOIN source_links ON source_links.title=LEFT(requests.source, LENGTH(source_links.title))"),


	// set filter
	if( szFilter.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE (requests.user_id = users.id)"), szQuery );
		szQuery = szTemp;
	}
	else
	{
		CString szTemp;
		//szTemp.Format( _T("%s WHERE (requests.user_id = users.id) AND %s"), szQuery, szFilter );
		szTemp.Format( _T("%s WHERE %s"), szQuery, szFilter );
		szQuery = szTemp;
	}

	// add group to prevent duplicates caused by source LIKE
	if( !appConfig.m_Options.bDisableSourceLinkCheck )
		szQuery += _T(" GROUP BY requests.id ");

	// add sort
	if( !szSort.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s ORDER BY %s"), szQuery, szSort );
		szQuery = szTemp;
	}

	// add limit
	if( nLimit > 0 )
	{
		CString szTemp;
		if( nRange >= 1 )
			szTemp.Format( _T("%s LIMIT %ld,%ld"), szQuery, nLimit, nRange );
		else
			szTemp.Format( _T("%s LIMIT %ld"), szQuery, nLimit );
		szQuery = szTemp;
	}

	TRACE( _T("Getting Map\n") );

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	TRACE( _T("Clearing Map\n") );

	REQUEST_DISPLAY_SET Request;
	// clear array
	pRequestSet->RemoveAll();

	LONG nIndex = nLimit;

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		// request
		Request.m_id = GetSQLLong(row[nRow++]);
		Request.m_staff_id = GetSQLLong(row[nRow++]);;
		Request.m_user_id = GetSQLLong(row[nRow++]);
		Request.m_external_id = GetSQLLong(row[nRow++]);
		Request.m_date_in.ParseDateTime(GetSQLString(row[nRow++]));
		Request.m_date_hold.ParseDateTime(GetSQLString(row[nRow++]));
		Request.m_date_out.ParseDateTime(GetSQLString(row[nRow++]));
		Request.m_status_id = GetSQLLong(row[nRow++]);
		Request.m_library_id = GetSQLLong(row[nRow++]);
		Request.m_collection_id = GetSQLLong(row[nRow++]);
		Request.m_atlibrary = GetSQLLong(row[nRow++]);
		Request.m_step_id = GetSQLLong(row[nRow++]);
		Request.m_request = GetSQLString(row[nRow++]);
		Request.m_source = GetSQLString(row[nRow++]);
		Request.m_priority_id = GetSQLLong(row[nRow++]);
		Request.m_method_id = GetSQLLong(row[nRow++]);
		Request.m_touched = GetSQLLong(row[nRow++]);
		Request.m_webcheck = GetSQLLong(row[nRow++]);
		Request.m_invoiced = GetSQLLong(row[nRow++]);
		Request.m_project = GetSQLString(row[nRow++]);
		Request.m_uid = GetSQLString(row[nRow++]);
		Request.m_location = GetSQLString(row[nRow++]);
		Request.m_link = GetSQLString(row[nRow++]);
		Request.m_retain = GetSQLLong(row[nRow++]);
		// user
		Request.m_users_email = GetSQLString(row[nRow++]);
		Request.m_users_lan_id = GetSQLString(row[nRow++]);
		Request.m_users_company = GetSQLString(row[nRow++]);
		Request.m_users_address1 = GetSQLString(row[nRow++]);
		// request links
		if( !appConfig.m_Options.bDisableSourceLinkCheck )
		{
			Request.m_request_link = GetSQLString(row[nRow++]);
			Request.m_source_link = GetSQLString(row[nRow++]);
			Request.m_source_notes = GetSQLString(row[nRow++]);
			Request.m_source_material = GetSQLInt(row[nRow++]);
		}
		else
		{
			Request.m_request_link = _T("");
			Request.m_source_link = _T("");
			Request.m_source_notes = _T("");
			Request.m_source_material = 0;
		}

		// insert
		pRequestSet->SetAt( nIndex, Request );
		nIndex++;
	}
	// get count
	ULONG nCount = (ULONG)mysql_num_rows( res );
	mysql_free_result( res );

	Close();

	//TRACE( _T("Request Count = %ld\n"), nCount );
	return nCount;
}

ULONG CMySQLEx::AddFullRequestMap(RequestMap *pRequestSet, LPCTSTR lpszSort, LPCTSTR lpszFilter, LONG nLimit, LONG nRange)
{
	USES_CONVERSION;

	CString szSort = lpszSort;
	CString szFilter = lpszFilter;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	// use source check
	if( appConfig.m_Options.bDisableSourceLinkCheck )
		szQuery = _T("SELECT requests.id, requests.staff_id, requests.user_id, requests.external_id, \
					 requests.date_in, requests.date_hold, requests.date_out, requests.status_id, requests.library_id, \
					 requests.collection_id, requests.atlibrary, \
					 requests.step_id, requests.request, requests.source, requests.priority_id, requests.method_id, \
					 requests.touched, requests.webcheck, requests.invoiced, requests.project, requests.uid, requests.location, requests.link, requests.retain, \
					 users.email, users.lan_id, users.company, users.address1 \
					 FROM requests LEFT JOIN users ON requests.user_id=users.id");
	else
		szQuery = _T("SELECT requests.id, requests.staff_id, requests.user_id, requests.external_id, \
					 requests.date_in, requests.date_hold, requests.date_out, requests.status_id, requests.library_id, \
					 requests.collection_id, requests.atlibrary, \
					 requests.step_id, requests.request, requests.source, requests.priority_id, requests.method_id, \
					 requests.touched, requests.webcheck, requests.invoiced, requests.project, requests.uid, requests.location, requests.link, requests.retain, \
					 users.email, users_lan_id, users.company, users.address1, \
					 request_links.link, \
					 source_links.link, source_links.notes, source_links.material \
					 FROM requests \
					 LEFT JOIN users ON requests.user_id=users.id \
					 LEFT JOIN request_links ON requests.uid=request_links.uid \
					 LEFT JOIN source_links ON requests.source LIKE CONCAT(source_links.title, '%%')");


	// set filter
	if( !szFilter.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s WHERE %s"), szQuery, szFilter );
		szQuery = szTemp;
	}

	// add sort
	if( !szSort.IsEmpty() )
	{
		CString szTemp;
		szTemp.Format( _T("%s ORDER BY %s"), szQuery, szSort );
		szQuery = szTemp;
	}

	// add limit
	if( nLimit >= 0 )
	{
		CString szTemp;
		if( nRange > 2 )
			szTemp.Format( _T("%s LIMIT %ld,%ld"), szQuery, nLimit, nRange );
		else
			szTemp.Format( _T("%s LIMIT %ld"), szQuery, nLimit );
		szQuery = szTemp;
	}

	// query
	if( mysql_real_query( &m_mysql, T2A(szQuery), _tcslen( szQuery )) != S_OK )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	// get results
	res = mysql_use_result( &m_mysql );
	if( !res )
	{
		CString szError(mysql_error( &m_mysql ));
		AfxMessageBox( szError );
		return FALSE;
	}

	REQUEST_DISPLAY_SET Request;
	LONG nIndex = nLimit;

	// populate array
	while( row = mysql_fetch_row( res ))
	{
		INT nRow = 0;
		// request
		Request.m_id = GetSQLLong(row[nRow++]);
		Request.m_staff_id = GetSQLLong(row[nRow++]);;
		Request.m_user_id = GetSQLLong(row[nRow++]);
		Request.m_external_id = GetSQLLong(row[nRow++]);
		Request.m_date_in.ParseDateTime(GetSQLString(row[nRow++]));
		Request.m_date_hold.ParseDateTime(GetSQLString(row[nRow++]));
		Request.m_date_out.ParseDateTime(GetSQLString(row[nRow++]));
		Request.m_status_id = GetSQLLong(row[nRow++]);
		Request.m_library_id = GetSQLLong(row[nRow++]);
		Request.m_collection_id = GetSQLLong(row[nRow++]);
		Request.m_atlibrary = GetSQLLong(row[nRow++]);
		Request.m_step_id = GetSQLLong(row[nRow++]);
		Request.m_request = GetSQLString(row[nRow++]);
		Request.m_source = GetSQLString(row[nRow++]);
		Request.m_priority_id = GetSQLLong(row[nRow++]);
		Request.m_method_id = GetSQLLong(row[nRow++]);
		Request.m_touched = GetSQLLong(row[nRow++]);
		Request.m_webcheck = GetSQLLong(row[nRow++]);
		Request.m_invoiced = GetSQLLong(row[nRow++]);
		Request.m_project = GetSQLString(row[nRow++]);
		Request.m_uid = GetSQLString(row[nRow++]);
		Request.m_location = GetSQLString(row[nRow++]);
		Request.m_link = GetSQLString(row[nRow++]);
		Request.m_retain = GetSQLLong(row[nRow++]);
		// user
		Request.m_users_email = GetSQLString(row[nRow++]);
		Request.m_users_lan_id = GetSQLString(row[nRow++]);
		Request.m_users_company = GetSQLString(row[nRow++]);
		Request.m_users_address1 = GetSQLString(row[nRow++]);
		// request links
		if( !appConfig.m_Options.bDisableSourceLinkCheck )
		{
			Request.m_request_link = GetSQLString(row[nRow++]);
			Request.m_source_link = GetSQLString(row[nRow++]);
			Request.m_source_notes = GetSQLString(row[nRow++]);
			Request.m_source_material = GetSQLInt(row[nRow++]);
		}
		else
		{
			Request.m_request_link = _T("");
			Request.m_source_link = _T("");
			Request.m_source_notes = _T("");
			Request.m_source_material = 0;
		}

		// insert
		pRequestSet->SetAt( nIndex, Request );
		nIndex++;
	}
	// get count
	ULONG nCount = (ULONG)mysql_num_rows( res );
	mysql_free_result( res );

	Close();

	return nCount;
}

BOOL CMySQLEx::GetFullRequest(ARTICLE_REQUEST *pRequest, LONGLONG nID)
{
	USES_CONVERSION;

	// connect
	if( !Connect() )
	{
		ASSERT( FALSE );
		return -1;
	}

	MYSQL_RES *res;
	MYSQL_ROW row;

	CString szQuery;
	if( appConfig.m_Options.bDisableSourceLinkCheck )
		szQuery.Format( _T("SELECT \
						   requests.id, requests.staff_id, requests.user_id, requests.date_in, requests.date_hold, requests.date_out, \
						   requests.status_id, requests.collection_id, requests.library_id, requests.atlibrary, requests.verified_id, requests.step_id, \
						   requests.request, requests.instructions, requests.information, requests.notes, \
						   requests.uid, requests.title, requests.source, requests.priority_id, requests.method_id, requests.ill_id, \
						   requests.file, requests.viewed, requests.redirected, requests.touched, requests.webcheck, requests.deleted, \
						   requests.invoiced, requests.type, requests.level, requests.project, requests.taxable, requests.overdue, \
						   requests.copyright, requests.canceled, requests.resubmitted, requests.finalized, requests.referrals, \
						   requests.manual, requests.material, \
						   requests.fee_copyright, requests.fee_service, requests.fee_misc, \
						   requests.external_id, requests.location, requests.link, requests.retain, \
						   users.id, users.level, users.name, users.pwd, users.email, users.lan_id, users.company, users.phone, users.fax, \
						   users.shipto, users.address1, users.address2, users.city, users.state, users.zipcode, users.country, \
						   users.billto, users.billaddress1, users.billaddress2, users.billcity, users.billstate, users.billzipcode, users.billcountry, \
						   users.altemail1, users.altemail2, users.altemail3, users.altemail4, users.altemail5, \
						   users.library_id, users.date_join, users.date_login, users.taxable, users.fee_max, users.activation_code, users.activated, \
						   users.notify, users.type, users.method_id, users.referrals, users.copyright, \
						   users.account_number, users.shipping_number, users.external, users.notes, users.external_id \
						   FROM requests JOIN users ON requests.user_id = users.id WHERE requests.id=%I64d"), nID );
	else
		szQuery.Format( _T("SELECT \
				   requests.id, requests.staff_id, requests.user_id, requests.date_in, requests.date_hold, requests.date_out, \
				   requests.status_id, requests.collection_id, requests.library_id, requests.atlibrary, requests.verified_id, requests.step_id, \
				   requests.request, requests.instructions, requests.information, requests.notes, \
				   requests.uid, requests.title, requests.source, requests.priority_id, requests.method_id, requests.ill_id, \
				   requests.file, requests.viewed, requests.redirected, requests.touched, requests.webcheck, requests.deleted, \
				   requests.invoiced, requests.type, requests.level, requests.project, requests.taxable, requests.overdue, \
				   requests.copyright, requests.canceled, requests.resubmitted, requests.finalized, requests.referrals, \
				   requests.manual, requests.material, \
				   requests.fee_copyright, requests.fee_service, requests.fee_misc, \
				   requests.external_id, requests.location, requests.link, requests.retain, \
				   users.id, users.level, users.name, users.pwd, users.email, users.lan_id, users.company, users.phone, users.fax, \
				   users.shipto, users.address1, users.address2, users.city, users.state, users.zipcode, users.country, \
				   users.billto, users.billaddress1, users.billaddress2, users.billcity, users.billstate, users.billzipcode, users.billcountry, \
				   users.altemail1, users.altemail2, users.altemail3, users.altemail4, users.altemail5, \
				   users.library_id, users.date_join, users.date_login, users.taxable, users.fee_max, users.activation_code, users.activated, \
				   users.notify, users.type, users.method_id, users.referrals, users.copyright, \
				   users.account_number, users.shipping_number, users.external, users.notes, users.external_id, \
				   source_links.notes \
				   FROM requests \
				   LEFT JOIN users ON requests.user_id = users.id \
				   LEFT JOIN source_links ON requests.source LIKE CONCAT(source_links.title, '%%') \
				   WHERE requests.id=%I64d"), nID );


	// set -1 for not found
	pRequest->m_id = -1;

	res = QueryForRow( szQuery, &row );

	if( res && res->row_count == 1 )
	{
		INT nRow = 0;
		// request
		pRequest->m_id = GetSQLLong(row[nRow++]);
		pRequest->m_staff_id = GetSQLLong(row[nRow++]);
		pRequest->m_user_id = GetSQLLong(row[nRow++]);
		pRequest->m_date_in.ParseDateTime(GetSQLString(row[nRow++]));
		pRequest->m_date_hold.ParseDateTime(GetSQLString(row[nRow++]));
		pRequest->m_date_out.ParseDateTime(GetSQLString(row[nRow++]));
		pRequest->m_status_id = GetSQLLong(row[nRow++]);
		pRequest->m_collection_id = GetSQLLong(row[nRow++]);
		pRequest->m_library_id = GetSQLLong(row[nRow++]);
		pRequest->m_atlibrary = GetSQLLong(row[nRow++]);
		pRequest->m_verified_id = GetSQLLong(row[nRow++]);
		pRequest->m_step_id = GetSQLLong(row[nRow++]);
		pRequest->m_request = GetSQLString(row[nRow++]);
		pRequest->m_instructions = GetSQLString(row[nRow++]);
		pRequest->m_information = GetSQLString(row[nRow++]);
		pRequest->m_notes = GetSQLString(row[nRow++]);
		pRequest->m_uid = GetSQLString(row[nRow++]);
		pRequest->m_title = GetSQLString(row[nRow++]);
		pRequest->m_source = GetSQLString(row[nRow++]);
		pRequest->m_priority_id = GetSQLLong(row[nRow++]);
		pRequest->m_method_id = GetSQLLong(row[nRow++]);
		pRequest->m_ill_id = GetSQLLong(row[nRow++]);
		pRequest->m_file = GetSQLString(row[nRow++]);
		pRequest->m_viewed = GetSQLLong(row[nRow++]);
		pRequest->m_redirected = GetSQLLong(row[nRow++]);
		pRequest->m_touched = GetSQLLong(row[nRow++]);
		pRequest->m_webcheck = GetSQLLong(row[nRow++]);
		pRequest->m_deleted = GetSQLLong(row[nRow++]);
		pRequest->m_invoiced = GetSQLLong(row[nRow++]);
		pRequest->m_type = GetSQLLong(row[nRow++]);
		pRequest->m_level = GetSQLLong(row[nRow++]);
		pRequest->m_project = GetSQLString(row[nRow++]);
		pRequest->m_taxable = GetSQLLong(row[nRow++]);
		pRequest->m_overdue = GetSQLLong(row[nRow++]);
		pRequest->m_copyright = GetSQLLong(row[nRow++]);
		pRequest->m_canceled = GetSQLLong(row[nRow++]);
		pRequest->m_resubmitted = GetSQLLong(row[nRow++]);
		pRequest->m_finalized = GetSQLLong(row[nRow++]);
		pRequest->m_referrals = GetSQLLong(row[nRow++]);
		pRequest->m_manual = GetSQLLong(row[nRow++]);
		pRequest->m_material = GetSQLLong(row[nRow++]);
		pRequest->m_fee_copyright = GetSQLDouble(row[nRow++]);
		pRequest->m_fee_service = GetSQLDouble(row[nRow++]);
		pRequest->m_fee_misc = GetSQLDouble(row[nRow++]);
		pRequest->m_external_id = GetSQLLong(row[nRow++]);
		pRequest->m_location = GetSQLString(row[nRow++]);
		pRequest->m_link = GetSQLString(row[nRow++]);
		pRequest->m_retain = GetSQLLong(row[nRow++]);

		pRequest->m_users_id = GetSQLLong(row[nRow++]);
		pRequest->m_users_level = GetSQLLong(row[nRow++]);
		pRequest->m_users_name = GetSQLString(row[nRow++]);
		pRequest->m_users_pwd = GetSQLString(row[nRow++]);
		pRequest->m_users_email = GetSQLString(row[nRow++]);
		pRequest->m_users_lan_id = GetSQLString(row[nRow++]);
		pRequest->m_users_company = GetSQLString(row[nRow++]);
		pRequest->m_users_phone = GetSQLString(row[nRow++]);
		pRequest->m_users_fax = GetSQLString(row[nRow++]);
		pRequest->m_users_shipto = GetSQLString(row[nRow++]);
		pRequest->m_users_address1 = GetSQLString(row[nRow++]);
		pRequest->m_users_address2 = GetSQLString(row[nRow++]);
		pRequest->m_users_city = GetSQLString(row[nRow++]);
		pRequest->m_users_state = GetSQLString(row[nRow++]);
		pRequest->m_users_zipcode = GetSQLString(row[nRow++]);
		pRequest->m_users_country = GetSQLString(row[nRow++]);
		pRequest->m_users_billto = GetSQLString(row[nRow++]);
		pRequest->m_users_billaddress1 = GetSQLString(row[nRow++]);
		pRequest->m_users_billaddress2 = GetSQLString(row[nRow++]);
		pRequest->m_users_billcity = GetSQLString(row[nRow++]);
		pRequest->m_users_billstate = GetSQLString(row[nRow++]);
		pRequest->m_users_billzipcode = GetSQLString(row[nRow++]);
		pRequest->m_users_billcountry = GetSQLString(row[nRow++]);
		pRequest->m_users_altemail1 = GetSQLString(row[nRow++]);
		pRequest->m_users_altemail2 = GetSQLString(row[nRow++]);
		pRequest->m_users_altemail3 = GetSQLString(row[nRow++]);
		pRequest->m_users_altemail4 = GetSQLString(row[nRow++]);
		pRequest->m_users_altemail5 = GetSQLString(row[nRow++]);
		pRequest->m_users_library_id = GetSQLLong(row[nRow++]);
		pRequest->m_users_date_join.ParseDateTime(GetSQLString(row[nRow++]));
		pRequest->m_users_date_login.ParseDateTime(GetSQLString(row[nRow++]));
		pRequest->m_users_taxable = GetSQLLong(row[nRow++]);
		pRequest->m_users_fee_max = GetSQLLong(row[nRow++]);
		pRequest->m_users_activation_code = GetSQLLong(row[nRow++]);
		pRequest->m_users_activated = GetSQLLong(row[nRow++]);
		pRequest->m_users_notify = GetSQLLong(row[nRow++]);
		pRequest->m_users_type = GetSQLLong(row[nRow++]);
		pRequest->m_users_method_id = GetSQLLong(row[nRow++]);
		pRequest->m_users_referrals = GetSQLLong(row[nRow++]);
		pRequest->m_users_copyright = GetSQLLong(row[nRow++]);
		pRequest->m_users_account_number = GetSQLString(row[nRow++]);
		pRequest->m_users_shipping_number = GetSQLString(row[nRow++]);
		pRequest->m_users_external = GetSQLInt(row[nRow++]);
		pRequest->m_users_notes = GetSQLString(row[nRow++]);
		pRequest->m_users_external_id = GetSQLLong(row[nRow++]);

		// request links
		if( !appConfig.m_Options.bDisableSourceLinkCheck )
		{
			pRequest->m_source_notes = GetSQLString(row[nRow++]);
		}
		else
		{
			pRequest->m_source_notes = _T("");
		}
	}
	mysql_free_result( res );

	Close();
	return TRUE;
}
